topic Re: DAX's PATH function equivalent Custom Column in Power Query in Quick Measures Gallery

DAX's PATH function equivalent Custom Column in Power Query

Zubair_Muhammad — Tue, 24 Sep 2019 07:43:14 GMT

This custom column formula in Power Query provides an equivalent of DAX's PATH function.

If you have many levels (PATHLENGTH is greater than say 10), it can save you time having to create calculated columns to get each PATHITEM

With Power Query, you can simply split the PATH into PATHITEMS with one click.

Just change the text in Red Color font in below formula according to your column names and previous step name. See the picture for guidance

=let //Define your columns below
 c=[Child],p=[Parent],mytable=#"Changed Type",pc="Parent",cc="Child" 
  in
let mylist={c} & List.Generate(()=>[x=0,y=p,w=1],each [w] > 0,each [z=[y], 
x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)
],
each [y])
        in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")

Re: DAX's PATH function equivalent Custom Column in Power Query

Daniil — Tue, 24 Sep 2019 11:23:11 GMT

Good work! Just to simplify the formula a bit:

let //Define your columns here
mytable=ChangedType,p="Parent",c="Child" 
  in
let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
],
each [y])
        in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")

Re: DAX's PATH function equivalent Custom Column in Power Query

Zubair_Muhammad — Tue, 24 Sep 2019 11:42:22 GMT

Thanks Daniil

Re: DAX's PATH function equivalent Custom Column in Power Query

asoysal — Thu, 12 Dec 2019 12:53:42 GMT

Thanks to Daniil and Zubair_Muhammad . I created the custom column,with no syntax error in the Custom Column creation step. However I receive an Error in the table view.

See below:

= let //Define your columns here
mytable=#"Changed Type",p=[#"DirectReportTo Asso. No."],c=[#"Asso. No."]
in
let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
],
each [y])
in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")

Re: DAX's PATH function equivalent Custom Column in Power Query

a1b1c1 — Fri, 27 Dec 2019 10:15:21 GMT

Hi Zubair_Muhammad, Daniil
Either of the steps are too time consuming, it's been running for about 45 minutes and still hasn't been able to complete this step. Any help?

Re: DAX's PATH function equivalent Custom Column in Power Query

justlogmein — Wed, 22 Dec 2021 03:27:54 GMT

I have tried this method and you are correct, this is much too inefficient. I have only 6,000 rows and we are talking hours to run. Did you find another solution?

Re: DAX's PATH function equivalent Custom Column in Power Query

Oscar_Mtz_V — Mon, 18 Apr 2022 07:20:35 GMT

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

Re: DAX's PATH function equivalent Custom Column in Power Query

Guido_Beulen — Thu, 04 Aug 2022 14:45:02 GMT

(Never mind: all others - pay attention to the previous step in the Query Editor which needs to be referenced... that was my error-cause. Performance is extremely show, though, using this Custom Column and then seperating it into levels for a hierarchy (I have eight levels in the hierarchy and a couple of thousand rows). Wonder if it will fix the issues I now face with Dax though!

I keep getting an Error as a result, what am I doing wrong?

= Table.AddColumn(#"Functionele accounts filteren", "Aangepast", each let //Define your columns here
mytable=#"Dim Medewerker",p="repto_id",c="res_id"
in
let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
],
each [y])
in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|"))

Re: DAX's PATH function equivalent Custom Column in Power Query

Anonymous — Mon, 07 Nov 2022 17:27:44 GMT

Zubair_Muhammad Oscar_Mtz_V

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 ?

Re: DAX's PATH function equivalent Custom Column in Power Query

Scott_Parker — Wed, 16 Nov 2022 20:40:03 GMT

I wrote one that could do ~100,000 rows in ~20 seconds. Appends a column "PATH" to a table. Inspired by this BIAccountant post: https://www.thebiccountant.com/2021/02/10/guest-post-using-list-accumulate-for-input-output-genealogy/, but modified to be recursive.

A record at the top of the hierarchy should have a null in the Parent field. There is no loop protection, so you don't want any records to be their own parents, their own grandparents, etc.

Note that you only invoke the function once on the table as whole, not row-by-row. Add a step along the lines of:

= fnAddPath(#"Previous Step", "ParentID Column Name", "SelfID Column Name")

(Self_Referential_Table as table, Parent_Column_Name as text, Self_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"}}),{"Child", "Parent"})) 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"}, {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(Record.Field(ResultOutput, "maxHeight") + 1,Record.Field(ResultOutput, "maxHeight") + 2, -1), each Number.ToText(_)), OutputTable = Table.AddColumn(Record.Field(ResultOutput, "resultTable"), "PATH", each Text.Combine(List.Transform(ListOfLevels, (x) => Record.Field(_, x)), "|")), OutputTable2 = Table.RemoveColumns(Table.RenameColumns( OutputTable, {{"0", Self_Column_Name}, {"1", Parent_Column_Name}}), List.RemoveMatchingItems(ListOfLevels,{"0","1"})) in OutputTable2

Probably the final Reformatting could be done more cleverly and in fewer lines of code.

Re: DAX's PATH function equivalent Custom Column in Power Query

mloyalka1996 — Wed, 16 Nov 2022 19:40:59 GMT

Hi Scott, Thank you for the code. I have a couple of question regarding the funvtion you have written. A small question what does it mean when u say "A topmost record should have a null in the Parent field". do i need to add a record ?

Re: DAX's PATH function equivalent Custom Column in Power Query

Scott_Parker — Wed, 16 Nov 2022 19:54:51 GMT

I rephrased it. I meant a record at the top of the hierarchy, meaning it has no parents, should have Parent = null, rather than Parent = Self because it would result in infinite recursion.

Re: DAX's PATH function equivalent Custom Column in Power Query

mloyalka1996 — Wed, 16 Nov 2022 20:18:39 GMT

thanks for that Scott. I created a coloumn called task_final. so whenevr Parent=self i replaced the value of child to null or kept the child value. Now when i am running the function with the following parameters. I still get an error. Any suggestion?

Re: DAX's PATH function equivalent Custom Column in Power Query

Scott_Parker — Wed, 16 Nov 2022 20:42:26 GMT

I have added clarification. Invoke the function on the table in one go, not for each row.

Re: DAX's PATH function equivalent Custom Column in Power Query

mloyalka1996 — Wed, 16 Nov 2022 20:51:21 GMT

thank you scott for the clarification. I am bit new to power bi . I am trying to run function as a whole but I am not able to . It will be a big help if you can provide some help on how I can run the function as a whole

Re: DAX's PATH function equivalent Custom Column in Power Query

toum — Thu, 22 Jun 2023 14:06:51 GMT

Hello, I know this was posted a while ago, but I would be grateful if someone could help me. How can I extract only the last level of the path, as shown in the figure below? I should only leave the lines in red and get rid of the other lines.

Re: DAX's PATH function equivalent Custom Column in Power Query

FedeMosquera — Fri, 14 Feb 2025 20:19:49 GMT

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

Re: DAX's PATH function equivalent Custom Column in Power Query

RealSir — Sat, 15 Feb 2025 11:51:33 GMT

last level means it's a leaf, existing as child but not as parent, easy to verify.