Power Query (M) Script in PowerBI for Log Analysis

The Complete Spirion Log Parser

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 (Update this path to your Gather Data folder)
    Source = Folder.Files("C:\Logs\GatherData\IDFLogFiles"),

    // 2. Filter for .log files and keep only necessary metadata
    FilterLogs = Table.SelectRows(Source, each ([Extension] = ".log")),
    RemovedOtherColumns = Table.SelectColumns(FilterLogs,{"Content", "Name"}),

    // 3. Extract the text lines from the binary files
    AddContent = Table.AddColumn(RemovedOtherColumns, "Custom", each Table.FromColumns({Lines.FromBinary([Content])})),
    ExpandedLogs = Table.ExpandTableColumn(AddContent, "Custom", {"Column1"}, {"RawText"}),

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

    // 5. Extract Header [Timestamp Level] and Message
    // This logic looks for the brackets [ ] used in Spirion logs
    AddHeader = Table.AddColumn(RemoveEmpty, "Header", each if Text.StartsWith([RawText], "[") then Text.BetweenDelimiters([RawText], "[", "]") else null),
    AddMessage = Table.AddColumn(AddHeader, "Message", each if Text.Contains([RawText], "] ") then Text.AfterDelimiter([RawText], "] ") else [RawText]),

    // 6. CHRONOLOGICAL STEP 1: Sort by Filename and Original Order
    // We add an initial index to preserve the physical order within the files
    AddInitialIndex = Table.AddIndexColumn(AddMessage, "PhysicalOrder", 1, 1, Int64.Type),

    // 7. CHRONOLOGICAL STEP 2: Fill Down Timestamps
    // This attaches the timestamp to multi-line errors (like stack traces)
    FillHeader = Table.FillDown(AddInitialIndex,{"Header"}),

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

    // 9. Convert to Date/Time type (Crucial for sorting)
    ChangedType = Table.TransformColumnTypes(SplitHeader,{
        {"TimestampString", type datetime}, 
        {"Level", type text}, 
        {"Message", type text},
        {"Name", type text}
    }),

    // 10. CHRONOLOGICAL STEP 3: Final Sort
    // We sort by the actual Time, then by the PhysicalOrder index as a tie-breaker
    FinalSort = Table.Sort(ChangedType,{{"TimestampString", Order.Ascending}, {"PhysicalOrder", Order.Ascending}}),

    // 11. Clean up
    FinalResult = Table.RemoveColumns(FinalSort,{"Content", "RawText", "PhysicalOrder"})
in
    FinalResult

Why this Script is Superior for Spirion logs

1. The Tie-Breaker (PhysicalOrder): Spirion agents are fast. They often write 50 lines of log in the same second. If you only sort by TimestampString, Power BI might randomize those 50 lines. By adding an Index (Step 6) and using it in the FinalSort (Step 10), we guarantee the logs appear exactly as they were written.
2. The "FillDown" Logic: If an agent throws a SQL error that is 20 lines long, only the first line has a timestamp. Step 7 ensures all 20 lines are grouped under that same timestamp so they don't get "lost" or filtered out.
3. Folder Awareness: It automatically handles IDF.log, IDF.log.1, and IDF.log.2 simultaneously. You don't have to import them one by one.
4. Data Integrity: It converts the TimestampString into a true Date/Time object. This allows you to use the "Time Slicer" in Power BI to zoom into a specific 5-minute window where a scan failed.

How to Verify the Sort

Once you load this into your report, create a Table Visual. Add TimestampString, Level, and Message. If you see a multi-line error where the message continues across several rows but the timestamp stays the same, the script is working perfectly!