Replicating PATH function of DAX in Power Query / M

DAX provides five functions (PATH,PATHLENGTH,PATHITEM,PATHITEMREVERSE,PATHCONTAINS) to help users manage data that is presented as a parent-child hierarchy in their models.

The most important of these functions is the PATH function which allows users to obtain the entire lineage of parents for each row.
Other 4 functions are then applied on the result of PATH function to obtain a browsable hierarchy in the data model

Check out the documentation on

https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax
https://www.daxpatterns.com/parent-child-hierarchies/

In this POST, I will cover

1) How to Use “M” to replicate PATH function
2) WHY USE “M” when we already have a DAX function

We will use a small dataset as follows for this post

1) How to Use “M” to replicate PATH function

We can use the Power of recursion to get the complete list of grand parents for each Child. Note that immediate parent is available for each Child.

So for example, look at the last row of the table in the picture above. “Reconciliation Expert” reports to “Manager IFRS”.
To get the first grand parent, we will have to filter column “Child Account” by “Manager IFRS” to get its parent “GM Account” and so on until grandest parent account is reached.

The picture below illustrates this

Following is the M code to achieve this requirement. Notice the use of myfunction. It recurses through the entire table for each Child Account to find all grandparents.

Since the PATH is presented from Grandest parent to Child, we use List.reverse to reverse the PATH we received from applying the function

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLNboMwEIRfBXHOARyMzRFVSVVVqGloe4lysIwVWaWm8k/UvH0NUWIWyAWE9pvZnV0Oh/hrF22lYoqLeBU/
    bd78M00pTfz7s46Pq4GoWStMxFQTVUx/CyvVKdBJQdOefr3RO901jlvZqZFlsi76L6ZYwwbwuRo1BlPQgqyDoedKzjunrJmChOZz0w8tmHH6MnNFlADXIdSVWs5HEpSHNXjFuPhQRdMMA1XZnIW20ogfcc/
    woGGWIyB9d6yV9hKVxicKqwILJhjh2RIAMVVQgmAbX+VOD/Mt4ZhksMH1pv1ZcZKM/pTKIyehb/fyBXi8ZTqcCNIZXqJftvt6jgLj2v0KfZamu9MTMQL4XvBOcdlK1geONn9ebRdEYZzjPw==", BinaryEncoding.Base64), 
    Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Child Account" = _t, #"Parent Account" = _t, Salary = _t, Nationality = _t]),

    ChangedType = Table.TransformColumnTypes(Source,{{"Child Account", type text}, {"Parent Account", type text}, {"Salary", Int64.Type}, {"Nationality", 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("Child Account","Parent Account",[Parent Account]),"|"),{""}
                                                        )
                                                 )
                                             &{[Parent Account],[Child Account]}
                                             ,
                                             "|"),"|"))
in
    Path

How could you use this code for your situation.

Easy. Just copy the myfunction step as above. In the Path step, just change the column names to the relevant column names in your table

Click here to download the pbix file

2) WHY USE “M” when we already have a DAX function

Will be posted soon

Share This: