DAX's PATH function equivalent Custom Column in Power Query
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