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

 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?

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

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-genealogy/, 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.

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

Hello, I know this was posted a while ago, but I would be grateful if someone could help me. How can I extract only the last level of the path, as shown in the figure below? I should only leave the lines in red and get rid of the other lines.