DAX's PATH function equivalent Custom Column in Power Query 12-27-2019 02:15 AM

[Zubair_Muhammad]

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

[Oscar_Mtz_V]

@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

[a1b1c1]

 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?

[justlogmein]

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?

[Daniil]

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]

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

[Zubair_Muhammad]

Thanks @Daniil