Performance issue when trying to replicate path function in M

[42]

Background

I export work items from Azure DevOps and would like to be able to filter out and load into Power BI only work items that is hierarchically ordered under a specific work item parent.

Problem

So in order to achieve the above I am trying to replicate the (DAX) Path function with M and power query. So that I can use the path as input to my filtering in the next step.  I have searched for a solution and found these two alternative ways of replicating the path function with M:

let
    ChangedType = Table.TransformColumnTypes(DevOps_import,{{"Work Item Id", type text}, {"Parent Work Item Id", type text}}),
    NewStep=Table.AddColumn(ChangedType, "Path", each let
    myfunction=(myvalue)=>
                    let
                    mylist=Table.SelectRows(ChangedType,each [Work Item Id]=myvalue)[Parent Work Item Id],
                    result=Text.Combine(mylist)
                    in
                    if result= null or result ="" then "" else if @myfunction(result)=null or @myfunction(result)="" then result else result & "|" & @ myfunction(result)
in
Text.Combine(List.Reverse(List.RemoveItems({[Work Item Id]}&{[Parent Work Item Id]}&Text.Split(myfunction([Parent Work Item Id]),"|"),{"",null})),"|"))
in
    ChangedType

or alternatively

let
    ChangedType = Table.TransformColumnTypes(DevOps_import,{{"Work Item Id", type text}, {"Parent", type text}}),

     myfunction=(ChildCol,ParentCol,CurrentParent)=>
                    let
                    mylist=Table.Column(Table.SelectRows(ChangedType,each Record.Field(_,ChildCol)=CurrentParent),ParentCol),
                    result=Text.Combine(mylist)
                    in
                  
                    Text.TrimEnd(
                    if result ="" then ""  else @ result & "|" & @ myfunction(ChildCol,ParentCol,result),
                   "|"),                          

    Path=Table.AddColumn(ChangedType,
                            "Path", each
                                    Text.Trim(
                                    Text.Combine(
                                    List.Reverse(
                                        List.RemoveItems(
                                        Text.Split(myfunction("Work Item Id","Parent",[Parent]),"|"),{""}
                                                        )
                                                 )
                                             &{[Parent],[Work Item Id]}
                                             ,
                                             "|"),"|"))
in
    Path

The problem is that both alternatives gives huge performance issues and being a newbie at this I don't have the knowledge to solve it myself. The DevOps_import table referenced has around 20 000 rows and is based on a import from Azure DevOps.

I'm thinking the solution would be in doing some itermediate buffering or indexing or similar but I have no idea on how to achieve that. Any help would be very much appreciated!

[ImkeF]

Hi @42  

in order to determine the PATH-result you have to read all rows from your source first. So just to be clear that this approach would only help you to load less data into your data model.

Recursion using the @-sign in is said to be slow in Power Query. Using List.Generate on buffered (!) inputs is often much faster. 
Some time ago, I've created a parent-child-function that you can find here: https://gist.githubusercontent.com/ImkeF/3a6b50e705bfbdbcb3480b6be505322a/raw/179e0bd0bced37328e92e5...  (not the best coding style, but has served me well so far...)