Parse Every Primavera P6 XER in a Folder into Power BI

Goal: Load every Primavera P6 .xer file (Baseline + Weekly Updates) from a folder, parse them automatically with Power Query, and visualize project progress dynamically in Power BI — no manual imports needed.

---

🚀 Why This Approach

Project teams often export weekly XER snapshots from Primavera P6.
But comparing them manually is time-consuming — especially across multiple projects or baselines.

With this Power BI + Power Query setup:

• 🔁 Every .xer in your folder (Baseline + Updates) loads automatically
• 🧩 Each file’s schema aligns even if some tables change or are missing
• 📆 Metadata (VersionId, SnapshotDate, Week, etc.) is added automatically
• 📊 You can visualize progress trends, variances, and earned value metrics over time

---

🧱 1. Create a Parameter for Folder Path

In Power Query, create a Text parameter named PARAM_XERFolderPath.

// PARAM_XERFolderPath
"EDIT THIS PATH"   // e.g., "C:\Data\PrimaveraXER"

💡 Tip: Make it easily editable so you can switch between Baseline, Updates, or Test folders quickly.

---

🧩 2. Create the Function ParseXER

This function converts a binary XER file into a navigation table with {Table Name, Data}.

---

📦 3. Create the Query XER_AllTables

This query scans the folder, parses every .xer, injects metadata, and merges all tables.

// XER_AllTables
let
    XERRoot = PARAM_XERFolderPath,
    Source = Folder.Files(XERRoot),

    // Filter only .xer files
    Filtered = Table.SelectRows(Source, each (try Text.EndsWith(Text.Lower([Name]), ".xer") otherwise false) and (try not Text.StartsWith([Name], "~$") otherwise true)),

    // Add metadata
    WithType  = Table.AddColumn(Filtered, "VersionType", each if Text.Contains(Text.Lower([Folder Path]), "baseline") then "Baseline" else "Update"),
    WithDate  = Table.AddColumn(WithType, "SnapshotDate", each DateTime.Date([Date modified])),
    WithWeek  = Table.AddColumn(WithDate, "SnapshotWeek", each Date.StartOfWeek([SnapshotDate], Day.Monday)),
    WithVerId = Table.AddColumn(WithWeek, "VersionId", each Date.ToText([SnapshotDate],"yyyyMMdd") & " | " & [VersionType] & " | " & [Name]),

    // Parse
    Parsed = Table.AddColumn(WithVerId, "Nav", each ParseXER([Content])),
    Expanded = Table.ExpandTableColumn(Parsed, "Nav", {"Table Name","Data"}),

    // Inject metadata into each Data table
    Injected = Table.AddColumn(Expanded, "DataWithMeta", each
        let t = [Data] in
            Table.AddColumn(
                Table.AddColumn(
                    Table.AddColumn(
                        Table.AddColumn(t, "VersionId", each [VersionId]),
                    "VersionType", each [VersionType]),
                "SnapshotDate", each [SnapshotDate]),
            "SnapshotWeek", each [SnapshotWeek])
    ),

    Injected2 = Table.AddColumn(Injected, "DataFinal", each Table.AddColumn([DataWithMeta], "FileName", (r)=> [Name])),

    // Combine all XER tables across versions
    KeepCols = Table.SelectColumns(Injected2, {"Table Name","DataFinal"}),
    Grouped = Table.Group(KeepCols, {"Table Name"}, {{"Data", each
        let
            children = [DataFinal],
            allcols  = List.Accumulate(children, {}, (s,t) => List.Union({s, Table.ColumnNames(t)})),
            aligned  = List.Transform(children, each Table.SelectColumns(_, allcols, MissingField.UseNull)),
            combined = if List.Count(aligned)=0 then #table({}, {}) else Table.Combine(aligned)
        in combined
    }}),

    WithCount = Table.AddColumn(Grouped, "Row Count", each Table.RowCount([Data])),
    Sorted = Table.Sort(WithCount, {{"Table Name", Order.Ascending}})
in
    Sorted

---

⚙️ 4. Create Helper Function fxGetXER

// fxGetXER
(TableName as text) as table =>
let
    src = XER_AllTables,
    match = Table.SelectRows(src, each [Table Name] = TableName),
    out = if Table.RowCount(match)=0 then #table({}, {}) else match{0}[Data]
in
    out

Example usage:
fxGetXER("TASK") → returns all task data from all snapshots, with metadata included.

---

📊 5. Example: Create Friendly Query for Tasks

// TASK_friendly
let
    Source = fxGetXER("TASK"),
    Types = Table.TransformColumnTypes(Source, {
        {"task_id", Int64.Type}, {"proj_id", Int64.Type},
        {"target_start_date", type date}, {"target_end_date", type date},
        {"act_start_date", type date}, {"act_end_date", type date}
    }),
    AddKeys = Table.AddColumn(Types, "TaskKey", each [VersionId] & "|" & Text.From([task_id])),
    Renamed = Table.RenameColumns(AddKeys, {
        {"task_code", "Activity ID"},
        {"task_name", "Activity Name"},
        {"target_start_date", "Planned Start"},
        {"target_end_date", "Planned Finish"},
        {"act_start_date", "Actual Start"},
        {"act_end_date", "Actual Finish"},
        {"phys_complete_pct", "Physical % Complete"}
    }, MissingField.Ignore)
in
    Renamed

---

📈 6. Refresh and Explore

• Load all queries into the Power BI model
• Add relationships:
  • PROJECT → WBS → TASK
  • TASK → TASKRSRC, TASKACTV, TASKPRED
• Add measures (SPI, CPI, % Complete, etc.)
• Visualize baseline vs current progress dynamically by VersionId or SnapshotDate

---

🧠 7. Key Benefits

✅ Works with any number of .xer files
✅ Automatically detects Baseline vs Updates
✅ Handles missing tables/columns
✅ Merges schemas safely
✅ Adds version metadata for trend analysis
✅ Fully no-code refresh each week

---

🧩 8. Download the Full Template

Download the ready-to-use Power BI pack:

Includes:

• ParseXER.m
• XER_AllTables.m
• fxGetXER.m
• Example friendly queries
• Relationships and measures
• README setup guide

---

✍️ 9. About the Author

Mohamed Sabbah — Project Controls Manager | Power BI & Primavera Integration Expert
💼 Helping construction professionals automate planning workflows.
🌐 mohamedsabbah.com
🔗 Follow me on LinkedIn for more Power BI + P6 automations.