A Power Query M function that summarises Power Query diagnostics data, making it easier to interpret
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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