DAX's PATH function equivalent Custom Column in Power Query 11-16-2022 08:08 AM - last edited 11-16-2022 12:40 PM

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

[Scott_Parker]

I wrote one that could do ~100,000 rows in ~20 seconds. Appends a column "PATH" to a table. Inspired by this BIAccountant post: https://www.thebiccountant.com/2021/02/10/guest-post-using-list-accumulate-for-input-output-genealog... but modified to be recursive.

A record at the top of the hierarchy should have a null in the Parent field. There is no loop protection, so you don't want any records to be their own parents, their own grandparents, etc.

Note that you only invoke the function once on the table as whole, not row-by-row. Add a step along the lines of:

= fnAddPath(#"Previous Step", "ParentID Column Name", "SelfID Column Name")

(Self_Referential_Table as table,  Parent_Column_Name as text,  Self_Column_Name as text) =>
let
    #"Renamed Columns" = Table.RenameColumns(Self_Referential_Table, {{Self_Column_Name, "0"}, {Parent_Column_Name, "1"}}),
    Buffered = Table.Buffer(Table.SelectColumns(
            Table.RenameColumns(Self_Referential_Table,{{Self_Column_Name, "Child"},{Parent_Column_Name, "Parent"}}),{"Child", "Parent"}))
in
    let
        GetParents = (state as table, currentLevelFromLeaf as number) =>
        let
            NextParents = Table.ExpandTableColumn(
                                Table.NestedJoin(state, {Text.From(currentLevelFromLeaf)},
                                Buffered, {"Child"}, "NextLevel", JoinKind.LeftOuter),
                            "NextLevel", {"Parent"}, {Text.From(currentLevelFromLeaf + 1)}),
            result = if List.NonNullCount(Table.Column(NextParents,Text.From(currentLevelFromLeaf + 1))) = 0 then
                [resultTable = NextParents, maxHeight = currentLevelFromLeaf]
            else
                @GetParents(NextParents, currentLevelFromLeaf + 1)
        in
            result,

        //Reformat the Output
        ResultOutput = GetParents(#"Renamed Columns", 1),
        ListOfLevels = List.Transform(List.Numbers(Record.Field(ResultOutput, "maxHeight") + 1,Record.Field(ResultOutput, "maxHeight") + 2, -1), each Number.ToText(_)),
        OutputTable = Table.AddColumn(Record.Field(ResultOutput, "resultTable"), "PATH", 
            each Text.Combine(List.Transform(ListOfLevels, (x) => Record.Field(_, x)), "|")),
        OutputTable2 = Table.RemoveColumns(Table.RenameColumns(
            OutputTable, {{"0", Self_Column_Name}, {"1", Parent_Column_Name}}),
            List.RemoveMatchingItems(ListOfLevels,{"0","1"}))
    in
        OutputTable2

Probably the final Reformatting could be done more cleverly and in fewer lines of code.

[mloyalka1996]

Hi Scott, Thank you for the code. I have a couple of question regarding the funvtion you have written. A small question what does it mean when u say "A topmost record should have a null in the Parent field". do i need to add a record ?

[Scott_Parker]

I rephrased it. I meant a record at the top of the hierarchy, meaning it has no parents, should have Parent = null, rather than Parent = Self because it would result in infinite recursion.

[mloyalka1996]

thanks for that Scott. I created a coloumn called task_final. so whenevr Parent=self i replaced the value of child to null or kept the child value. Now when i am running the function with the following parameters. I still get an error. Any suggestion?

[Scott_Parker]

I have added clarification. Invoke the function on the table in one go, not for each row.

[mloyalka1996]

thank you scott for the clarification. I am bit new to power bi . I am trying to run function as a whole but I am not able to . It will be a big help if you can provide some help on how I can run the function as a whole

[Guido_Beulen]

(Never mind: all others - pay attention to the previous step in the Query Editor which needs to be referenced... that was my error-cause. Performance is extremely show, though, using this Custom Column and then seperating it into levels for a hierarchy (I have eight levels in the hierarchy and a couple of thousand rows). Wonder if it will fix the issues I now face with Dax though!

I keep getting an Error as a result, what am I doing wrong? 

= Table.AddColumn(#"Functionele accounts filteren", "Aangepast", each let //Define your columns here
mytable=#"Dim Medewerker",p="repto_id",c="res_id"
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,""})),"|"))

[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

[nokjaknow]

@Zubair_Muhammad @Oscar_Mtz_V  

Do you know how to modify it to display list of Salary instead of values of Parent column, but path is still related to Child and Parent columns ?

[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