---
title: "Power Query (M) Script in PowerBI for Log Analysis"
url: "https://dm0bkdes5q7a2ls.productfruits.help/en/article/power-query-m-script-in-powerbi-for-log-analysis"
language: "en"
last_updated: "2026-05-15T19:22:24.796Z"
---

# Power Query (M) Script in PowerBI for Log Analysis

This Power Query (M) script is the "Gold Standard" for Spirion log analysis. It combines all of the following: Folder connection, Filename tracking, Multi-line error handling (Fill Down), and Chronological sorting using a tie-breaker Index.

## **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!