DAX's PATH function equivalent Custom Column in Power Query
Zubair_Muhammad , thanks a lot!
Just a small note for those that might be working with records with orphan records (Parent = null), you might need to fill this with the same "child id". This code can come in handy:
= Table.ReplaceValue(#"Replaced Errors",null, each _[Child],Replacer.ReplaceValue,{"Parent"})
Cheers!
Oscar
Don't forget to follow my BI blog in www.bibb.pro
Do you know how to modify it to display list of Salary instead of values of Parent column, but path is still related to Child and Parent columns ?
Hi, I've modified the function adding a fourth parameter which is the Column Name you want the path to use for final display:
Example when invoking the function (table, "ManagerId", "Id", "Column Name for PATH")(Self_Referential_Table as table, Parent_Column_Name as text, Self_Column_Name as text, Path_Column_Name as text) =>
let
#"Renamed Columns" = Table.RenameColumns(Self_Referential_Table, {{Self_Column_Name, "0"}, {Parent_Column_Name, "1"}}),
Buffered = Table.Buffer(Table.SelectColumns(
Table.RenameColumns(Self_Referential_Table,{{Self_Column_Name, "Child"},{Parent_Column_Name, "Parent"}, {Path_Column_Name, "PathValue"}}),{"Child", "Parent", "PathValue"}))
in
let
GetParents = (state as table, currentLevelFromLeaf as number) =>
let
NextParents = Table.ExpandTableColumn(
Table.NestedJoin(state, {Text.From(currentLevelFromLeaf)},
Buffered, {"Child"}, "NextLevel", JoinKind.LeftOuter),
"NextLevel", {"Parent", "PathValue"}, {Text.From(currentLevelFromLeaf + 1), "PathValue" & Text.From(currentLevelFromLeaf + 1)}),
result = if List.NonNullCount(Table.Column(NextParents, Text.From(currentLevelFromLeaf + 1))) = 0 then
[resultTable = NextParents, maxHeight = currentLevelFromLeaf]
else
@GetParents(NextParents, currentLevelFromLeaf + 1)
in
result,// Reformat the Output
ResultOutput = GetParents(#"Renamed Columns", 1),
ListOfLevels = List.Transform(List.Numbers(1, Record.Field(ResultOutput, "maxHeight")), each "PathValue" & Text.From(_)),
OutputTable = Table.AddColumn(Record.Field(ResultOutput, "resultTable"), "PATH",
each Text.Combine(List.Transform(ListOfLevels, (x) => try Record.Field(_, x) otherwise ""), "|")),
OutputTable2 = Table.RemoveColumns(Table.RenameColumns(
OutputTable, {{"0", Self_Column_Name}, {"1", Parent_Column_Name}}),
List.RemoveMatchingItems(List.Transform(List.Numbers(1, Record.Field(ResultOutput, "maxHeight")), each Text.From(_)), {"0", "1"})),
// Keep only original columns and PATH
FinalOutput = Table.SelectColumns(OutputTable2, Table.ColumnNames(Self_Referential_Table) & {"PATH"})
in
FinalOutput
