cancel
Showing results for
Did you mean:

DAX's PATH function equivalent Custom Column in Power Query Zubair_Muhammad
Super User
899 Views
Highlighted Super User

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

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

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

Thanks @Daniil