---
title: "Example of a Power Query Script in PowerBI"
url: "https://dm0bkdes5q7a2ls.productfruits.help/en/article/provide-an-example-of-a-power-query-script"
language: "en"
last_updated: "2026-05-15T18:32:26.445Z"
---

# Example of a Power Query Script in PowerBI

This Power Query (M) script is designed specifically for a Spirion Agent Log (like IDF.log or EPS.log). It handles the most common challenges: removing empty lines, splitting the timestamp, and "filling down" timestamps for multi-line error messages.

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