Have you already worked out the clash report as a table source?
That was a somewhat painful experience earlier as I’m putting a demo together:
let
// Load XML source
Source = Xml.Document(File.Contents("C:\Users\jonat\Desktop\Snowdon.xml")),
// Navigate through XML structure to get clash tests
#"Expanded Level1" = Table.ExpandTableColumn(Source, "Value", {"Name", "Value"}, {"Level1_Name", "Level1_Value"}),
#"Expanded Level2" = Table.ExpandTableColumn(#"Expanded Level1", "Level1_Value", {"Name", "Value"}, {"Level2_Name", "Level2_Value"}),
#"Expanded Level3" = Table.ExpandTableColumn(#"Expanded Level2", "Level2_Value", {"Name", "Attributes", "Value"}, {"Level3_Name", "Level3_Attributes", "Level3_Value"}),
// Filter to get individual clash tests
ClashTests = Table.SelectRows(#"Expanded Level3", each ([Level2_Name] = "clashtests" and [Level3_Name] = "clashtest")),
// Extract clash test attributes from the attributes table
#"Added ClashTestName" = Table.AddColumn(ClashTests, "ClashTestName", each
try Table.SelectRows([Level3_Attributes], each [Name] = "name"){0}[Value] otherwise null
),
// Get clash results and expand to clash groups
#"Added ClashGroups" = Table.AddColumn(#"Added ClashTestName", "ClashGroups", each
let
ClashTestValueTable = try [Level3_Value] otherwise #table({},{})
in
try Table.SelectRows(ClashTestValueTable, each [Name] = "clashresults"){0}[Value] otherwise #table({},{})
),
// Filter out tests with no results and expand clash groups
#"Filtered Results" = Table.SelectRows(#"Added ClashGroups", each Table.RowCount([ClashGroups]) > 0),
#"Expanded ClashGroups" = Table.ExpandTableColumn(#"Filtered Results", "ClashGroups", {"Name", "Attributes", "Value"}, {"GroupElement", "GroupAttributes", "GroupValue"}),
// Extract clash group information
#"Added GroupName" = Table.AddColumn(#"Expanded ClashGroups", "ClashGroupName", each
try Table.SelectRows([GroupAttributes], each [Name] = "name"){0}[Value] otherwise null
),
#"Added GroupStatus" = Table.AddColumn(#"Added GroupName", "GroupStatus", each
try Table.SelectRows([GroupAttributes], each [Name] = "status"){0}[Value] otherwise null
),
// Get individual clash results from within each group
#"Added IndividualClashes" = Table.AddColumn(#"Added GroupStatus", "IndividualClashes", each
let
GroupValueTable = try [GroupValue] otherwise #table({},{})
in
try Table.SelectRows(GroupValueTable, each [Name] = "clashresults"){0}[Value] otherwise #table({},{})
),
// Expand individual clash results
#"Expanded IndividualClashes" = Table.ExpandTableColumn(#"Added IndividualClashes", "IndividualClashes", {"Name", "Attributes", "Value"}, {"ClashResultElement", "ClashResultAttributes", "ClashResultValue"}),
// Extract individual clash result information
#"Added ClashResultName" = Table.AddColumn(#"Expanded IndividualClashes", "ClashResultName", each
try Table.SelectRows([ClashResultAttributes], each [Name] = "name"){0}[Value] otherwise null
),
#"Added ResultStatus" = Table.AddColumn(#"Added ClashResultName", "ResultStatus", each
try Table.SelectRows([ClashResultAttributes], each [Name] = "status"){0}[Value] otherwise null
),
// Extract created date from individual clash result
#"Added CreatedDate" = Table.AddColumn(#"Added ResultStatus", "CreatedDate", each
let
ClashResultValueTable = try [ClashResultValue] otherwise #table({},{})
in
try
let
CreatedDateRow = Table.SelectRows(ClashResultValueTable, each [Name] = "createddate"){0},
DateTable = CreatedDateRow[Value]{0},
DateAttributes = DateTable[Attributes],
Year = try Number.FromText(Table.SelectRows(DateAttributes, each [Name] = "year"){0}[Value]) otherwise 2025,
Month = try Number.FromText(Table.SelectRows(DateAttributes, each [Name] = "month"){0}[Value]) otherwise 1,
Day = try Number.FromText(Table.SelectRows(DateAttributes, each [Name] = "day"){0}[Value]) otherwise 1,
Hour = try Number.FromText(Table.SelectRows(DateAttributes, each [Name] = "hour"){0}[Value]) otherwise 0,
Minute = try Number.FromText(Table.SelectRows(DateAttributes, each [Name] = "minute"){0}[Value]) otherwise 0,
Second = try Number.FromText(Table.SelectRows(DateAttributes, each [Name] = "second"){0}[Value]) otherwise 0
in
#datetime(Year, Month, Day, Hour, Minute, Second)
otherwise null
),
// Get clash objects from individual clash results
#"Added ClashObjects" = Table.AddColumn(#"Added CreatedDate", "ClashObjects", each
let
ClashResultValueTable = try [ClashResultValue] otherwise #table({},{})
in
try Table.SelectRows(ClashResultValueTable, each [Name] = "clashobjects"){0}[Value] otherwise #table({},{})
),
// Expand clash objects (this creates one row per clash object)
#"Expanded ClashObjects" = Table.ExpandTableColumn(#"Added ClashObjects", "ClashObjects", {"Name", "Value"}, {"ObjectElement", "ObjectValue"}),
// Add index to identify first vs second clash object
#"Added Index" = Table.AddIndexColumn(#"Expanded ClashObjects", "ObjectIndex", 0, 1, Int64.Type),
// Add Left/Right based on object position (first = Left, second = Right)
#"Added LeftRight" = Table.AddColumn(#"Added Index", "LeftRight", each
let
// Group by clash result to get object position within each clash
ClashResultKey = [ClashTestName] & "|" & [ClashGroupName] & "|" & [ClashResultName],
CurrentIndex = [ObjectIndex]
in
// For each clash result, alternate between Left and Right
if Number.Mod(CurrentIndex, 2) = 0 then "Left" else "Right"
),
// Extract Element ID
#"Added ElementID" = Table.AddColumn(#"Added LeftRight", "ElementID", each
let
ObjectValueTable = try [ObjectValue] otherwise #table({},{})
in
try
let
ObjectAttrRow = Table.SelectRows(ObjectValueTable, each [Name] = "objectattribute"){0},
ObjectAttrTable = ObjectAttrRow[Value],
ValueRow = Table.SelectRows(ObjectAttrTable, each [Name] = "value"){0}
in
ValueRow[Value]
otherwise null
),
// Extract Layer (direct value)
#"Added Layer" = Table.AddColumn(#"Added ElementID", "Layer", each
let
ObjectValueTable = try [ObjectValue] otherwise #table({},{})
in
try Table.SelectRows(ObjectValueTable, each [Name] = "layer"){0}[Value] otherwise null
),
// Extract all smart tags as separate columns
#"Added ItemName" = Table.AddColumn(#"Added Layer", "ItemName", each
let
ObjectValueTable = try [ObjectValue] otherwise #table({},{})
in
try
let
SmartTagsRow = Table.SelectRows(ObjectValueTable, each [Name] = "smarttags"){0},
SmartTagsTable = SmartTagsRow[Value],
ItemNameTag = List.Select(Table.ToRecords(SmartTagsTable), each
let
TagTable = [Value],
NameRow = Table.SelectRows(TagTable, each [Name] = "name"){0}
in
NameRow[Value] = "Item Name"
){0},
ValueRow = Table.SelectRows(ItemNameTag[Value], each [Name] = "value"){0}
in
ValueRow[Value]
otherwise null
),
#"Added ItemGUID" = Table.AddColumn(#"Added ItemName", "ItemGUID", each
let
ObjectValueTable = try [ObjectValue] otherwise #table({},{})
in
try
let
SmartTagsRow = Table.SelectRows(ObjectValueTable, each [Name] = "smarttags"){0},
SmartTagsTable = SmartTagsRow[Value],
ItemGUIDTag = List.Select(Table.ToRecords(SmartTagsTable), each
let
TagTable = [Value],
NameRow = Table.SelectRows(TagTable, each [Name] = "name"){0}
in
NameRow[Value] = "Item GUID"
){0},
ValueRow = Table.SelectRows(ItemGUIDTag[Value], each [Name] = "value"){0}
in
ValueRow[Value]
otherwise null
),
#"Added SourceFileName" = Table.AddColumn(#"Added ItemGUID", "SourceFileName", each
let
ObjectValueTable = try [ObjectValue] otherwise #table({},{})
in
try
let
SmartTagsRow = Table.SelectRows(ObjectValueTable, each [Name] = "smarttags"){0},
SmartTagsTable = SmartTagsRow[Value],
SourceFileTag = List.Select(Table.ToRecords(SmartTagsTable), each
let
TagTable = [Value],
NameRow = Table.SelectRows(TagTable, each [Name] = "name"){0}
in
NameRow[Value] = "Item Source File Name"
){0},
ValueRow = Table.SelectRows(SourceFileTag[Value], each [Name] = "value"){0}
in
ValueRow[Value]
otherwise null
),
// Select final columns - one row per clash object
#"Selected Columns" = Table.SelectColumns(#"Added SourceFileName", {
"ClashTestName",
"ClashGroupName",
"ClashResultName",
"ResultStatus",
"CreatedDate",
"ElementID",
"Layer",
"ItemName",
"ItemGUID",
"SourceFileName",
"LeftRight"
}),
// Clean up data types
#"Changed Types" = Table.TransformColumnTypes(#"Selected Columns", {
{"ClashTestName", type text},
{"ClashGroupName", type text},
{"ClashResultName", type text},
{"ResultStatus", type text},
{"CreatedDate", type datetime},
{"ElementID", type text},
{"Layer", type text},
{"ItemName", type text},
{"ItemGUID", type text},
{"SourceFileName", type text}
}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Types",{{"ItemGUID", "GUID"}})
in
#"Renamed Columns"