DAX's PATH function equivalent Custom Column in Power Query

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 calculate...

Replicating DAX's Path function in M LG.pbix189 KB

Other

Daniil

Kudo Kingpin

6 years ago

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,""})),"|")

asoysal

New Member

6 years ago

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,""})),"|")