cancel
Showing results for 
Search instead for 
Did you mean: 

DAX's PATH function equivalent Custom Column in Power Query

Super User III
2313 Views
Super User III
Super User III

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

 

 

PATH.jpg

 

Try my new Power BI game Cross the River
Daniil Member
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 III
Super User III

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

Thanks @Daniil 

Try my new Power BI game Cross the River
asoysal New Member
New Member

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

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

a1b1c1 Frequent Visitor
Frequent Visitor

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

 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?