Example of a Power Query Script in PowerBI

How to Use this Script

Procedure:

1. In Power BI, go to Get Data > Blank Query.
2. Click on the View tab and select Advanced Editor.
3. Paste the code below (replacing the file path with your own).

let
    // 1. Load the raw log file
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\YourPath\IDF.log"))}),
    
    // 2. Rename the initial column
    RenameRaw = Table.RenameColumns(Source,{{"Column1", "RawText"}}),

    // 3. Remove completely empty rows
    RemoveEmpty = Table.SelectRows(RenameRaw, each ([RawText] <> "" and [RawText] <> null)),

    // 4. Extract the Timestamp and Level (everything inside the brackets [ ])
    // This creates a column with "2024-05-14 10:00:00 INF"
    ExtractHeader = Table.AddColumn(RemoveEmpty, "Header", each if Text.StartsWith([RawText], "[") then Text.BetweenDelimiters([RawText], "[", "]") else null),

    // 5. Extract the Message (everything after the closing bracket)
    ExtractMessage = Table.AddColumn(ExtractHeader, "Message", each if Text.Contains([RawText], "] ") then Text.AfterDelimiter([RawText], "] ") else [RawText]),

    // 6. IMPORTANT: Fill Down the Header
    // This ensures that multi-line errors (stack traces) inherit the timestamp of the line above them
    FillHeader = Table.FillDown(ExtractMessage,{"Header"}),

    // 7. Split the Header into Date/Time and Log Level
    // Assumes format: "YYYY-MM-DD HH:MM:SS LEVEL"
    SplitHeader = Table.SplitColumn(FillHeader, "Header", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"TimestampString", "Level"}),

    // 8. Convert Timestamp to actual Date/Time type
    // This allows Power BI to use time-based charts
    FinalTypes = Table.TransformColumnTypes(SplitHeader,{
        {"TimestampString", type datetime}, 
        {"Level", type text}, 
        {"Message", type text}
    }),

    // 9. Clean up: Remove the original RawText column
    CleanUp = Table.RemoveColumns(FinalTypes,{"RawText"}),
    
    // 10. Rename the final Timestamp column
    FinalResult = Table.RenameColumns(CleanUp,{{"TimestampString", "EventTime"}})
in
    FinalResult

What this Script Solves

• Multi-line Errors: Step 6 (Table.FillDown) is the "magic" step. If a Spirion log shows a long SQL error or Java stack trace where only the first line has a [Timestamp], this script attaches that timestamp to every line of the error so you don't lose data.
• Empty Lines: Step 3 removes the "junk" lines often found at the end of log files.
• Time Intelligence: Step 8 converts the text date into a real DateTime object. Without this, you cannot create line charts or use "Last 24 Hours" filters.
• Categorization: It separates the Level (INF, ERR, WRN) so you can immediately create a Pie Chart of "Error vs. Success."

Pro Tip

If you are parsing multiple files from a folder, the first line of the script would change to Source = Folder.Files("C:\YourFolder"), and you would apply these transformation steps inside the "Transform Sample File" function that Power BI generates automatically.

Script with added filename handling

To handle multiple files (like a "Gather Data" zip or rotated logs), you need to modify the script to use the Folder Connector.

This version of the script includes a Source.Name column so you can track exactly which file (for example, IDF.log, IDF.log.1) each log entry came from.

The "Multi-File" Power Query Script

Procedure:

1. In Power BI, go to Get Data > Blank Query.
2. Open the Advanced Editor and paste the following:

let
    // 1. Connect to the folder containing the logs
    // Replace the path below with your actual folder path
    Source = Folder.Files("C:\Logs\GatherData\IDFLogFiles"),

    // 2. Filter to only include .log files (ignores hidden files or zips)
    FilterLogs = Table.SelectRows(Source, each ([Extension] = ".log")),

    // 3. Keep only the Content (the file itself) and the Name (the filename)
    KeepColumns = Table.SelectColumns(FilterLogs,{"Content", "Name"}),

    // 4. Transform the binary content into text lines
    // This creates a nested table for each file
    AddContent = Table.AddColumn(KeepColumns, "Custom", each Table.FromColumns({Lines.FromBinary([Content])})),

    // 5. Expand the nested tables
    // This "stacks" all log lines from all files into one long list
    ExpandedLogs = Table.ExpandTableColumn(AddContent, "Custom", {"Column1"}, {"RawText"}),

    // 6. Remove completely empty rows
    RemoveEmpty = Table.SelectRows(ExpandedLogs, each ([RawText] <> "" and [RawText] <> null)),

    // 7. Extract the Header [Timestamp Level]
    ExtractHeader = Table.AddColumn(RemoveEmpty, "Header", each if Text.StartsWith([RawText], "[") then Text.BetweenDelimiters([RawText], "[", "]") else null),

    // 8. Extract the Message
    ExtractMessage = Table.AddColumn(ExtractHeader, "Message", each if Text.Contains([RawText], "] ") then Text.AfterDelimiter([RawText], "] ") else [RawText]),

    // 9. Fill Down the Header (Crucial for multi-line errors)
    // We sort by Name and then Fill Down to ensure we don't bleed timestamps between different files
    SortedFiles = Table.Sort(ExtractMessage,{{"Name", Order.Ascending}}),
    FillHeader = Table.FillDown(SortedFiles,{"Header"}),

    // 10. Split Header into Timestamp and Level
    SplitHeader = Table.SplitColumn(FillHeader, "Header", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"TimestampString", "Level"}),

    // 11. Final Data Typing
    FinalTypes = Table.TransformColumnTypes(SplitHeader,{
        {"Name", type text},
        {"TimestampString", type datetime}, 
        {"Level", type text}, 
        {"Message", type text}
    }),

    // 12. Clean up: Remove the binary Content and RawText columns
    FinalResult = Table.RemoveColumns(FinalTypes,{"Content", "RawText"})
in
    FinalResult

Key Features of this Script

• Name Column: You now have a column showing exactly which file produced the log line. You can use this as a Slicer in your report to toggle between IDF.log and IDF.log.1.
• Binary Handling: It uses Lines.FromBinary to read the files directly without needing to manually combine them.
• Safe Fill Down: By sorting by Name before the FillDown step, we ensure that a timestamp from the end of File_A doesn't accidentally get applied to a blank line at the start of File_B.
• Extension Filtering: It automatically ignores any non-log files that might be in the folder (like .txt or .xml files often found in Gather Data zips).

How to use the Filename in your Report

Procedure:

1. The "Log Rotation" View: Create a Bar Chart with Name on the Axis and Count of Rows on the Values. This shows you which log files are the "heaviest" (usually the ones with the most errors).
2. The Timeline: Use the TimestampString on your X-Axis and put Name in the Legend. This shows you the chronological flow of events as they moved from the rotated logs into the current log.

Tip: If you are analyzing a Distributed Scan, the filenames often include the Agent Name. This script enables you to compare multiple different Agents side-by-side just by looking at the Name column!