A Power Query M function that summarises Power Query diagnostics data, making it easier to interpret
(Input as table) as table => | |
let | |
Source = Input, | |
#"Expanded Additional Info" = Table.ExpandRecordColumn(Source, "Additional Info", {"Message"}, {"Message"}), | |
#"Calculated Total Seconds" = Table.TransformColumns(#"Expanded Additional Info",{{"Exclusive Duration", Duration.TotalSeconds, type number}}), | |
#"Sorted Rows" = Table.Sort(#"Calculated Total Seconds",{{"Id", Order.Ascending},{"Start Time", Order.Ascending}}), | |
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Id", "Query", "Category", "Operation", "Start Time", "End Time", "Exclusive Duration (%)", "Exclusive Duration", "Data Source Query", "Message", "Row Count", "Content Length", "Path", "Group Id"}), | |
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Message", type text}}), | |
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"Missing",Replacer.ReplaceValue,{"Path"}), | |
BufferedTable = Table.Buffer(#"Replaced Value"), | |
GetAllChildRows = (CurrentId, CurrentPath) => | |
Table.SelectRows(BufferedTable, each [Path]<>"Missing" and [Id]=CurrentId and Text.StartsWith([Path], CurrentPath)), | |
AddTotalED = Table.AddColumn(#"Replaced Value", "Exclusive Duration (Including Child Operations)", each List.Sum(GetAllChildRows([Id],[Path])[Exclusive Duration]), type number), | |
AddTotalEDPct = Table.AddColumn(AddTotalED, "Exclusive Duration (%) (Including Child Operations)", each List.Sum(GetAllChildRows([Id],[Path])[#"Exclusive Duration (%)"]), Percentage.Type), | |
#"Inserted Text Before Delimiter" = Table.AddColumn(AddTotalEDPct, "Parent Path", each Text.BeforeDelimiter([Path], "/", {0, RelativePosition.FromEnd}), type text), | |
#"Added Custom" = Table.AddColumn(#"Inserted Text Before Delimiter", "Child Operations", each | |
let | |
CurrentPath = [Path], | |
CurrentId = [Id], | |
ChildRows = | |
Table.SelectRows( | |
@#"Added Custom", | |
each | |
[Path]<>"Missing" and [Parent Path]=CurrentPath and [Id] = CurrentId | |
), | |
Output = if Table.RowCount(ChildRows)=0 then null else ChildRows | |
in | |
Output), | |
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Path] = "0" or [Path]="Missing")), | |
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Parent Path"}) | |
in | |
#"Removed Columns" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment