Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
6 years ago

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...
Other
Daniil's avatar
Daniil
Icon for Kudo Kingpin rankKudo 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,""})),"|")