Dynamically flatten Parent-Child Hierarchies in DAX and PowerBI

If you use DAX to flatten Parent-Child hierarchies you will end up with a table that has a static number of columns (like described here). If you need a dynamic solution instead, which creates just as many level-columns as there are needed for the current data, you can use DAX’s helper-tool Power Query (or Get Data in Excel) or the query-editor in PowerBI, which uses the language M.

Another advantage of this solution is that you can script the table creation in one step (only flaw: You still need to manually adjust your hierarchy though): But it saves time in creating the table, especially if you have many levels.

2 simple steps

  1. copy the following function,
  2. add a new step to your current table where you call this function, filling in the following parameters:
    • table name (which is the name of the previous step in your M-query)
    • name of the column with the child-key
    • name of the column with the parent-key
    • name of the column who’s values shall be shown in the levels (can also be child-key)

 

Call fnFlattenPCHierarchyFunction

 

And this is the code, which you can also download below:

Function Code

 

Downloads:

Download M-code:  Flatten PC Hierarchy M-code

Subscribers can also download the pbix-file, where this function in implemented and there’s also a query which enables you to follow-along the results of every step:

DynamicPCHierarchy.zip

 

Enjoy & stay queryious 😉

 

Comments (11) Write a comment

  1. Hi Imke

    I have been looking for something like this. I’ve tried to use it by copy-paste the code, but I get error when calling the function. The error provided is something along the lines of:

    Expression.Error: Column ‘1190’ in the table was not found.

    1190 is the value in the child column. I have tried both with Number and Text. The error is consistent on every line.
    I am using power query in Excel.

    Any idea what triggered this?

    Thanks and regards
    Nikolaj

    Reply

    • Hi Nikolaj,
      this looks as if you’re trying to call the function in an “Add-column”-s step. Then the reference to the column would actually not return a reference to a whole column, but just a record field. That would explain the error message.
      Pls check out my sample-file: There step “Custom1” looks like this: = fnFlattenPCHierarchy(Source, “NodeKey”, “ParentKey”, “Name”)
      There the function is called “naked” and not within an “Table.AddColumn”-frame.
      Pls let me know if this was the reason.
      Thx & kind regards, Imke

      Reply

    • Thanks for the response Nikolaj, glad it worked.
      May I ask for which kind of task your are using this technique?

      Reply

  2. Hi Imke,

    thanks for this nice piece of code. I really enjoyed going through your formulas.

    I guess, you agree that leaves are nodes that are not parents of any other node. Thus, for instance Julie is a leaf!

    Therefore, I have replaced this slip of the pen

    = Table.AddColumn(HierarchyDepth, “IsLeaf”, each if [HierarchyDepth]=MaxBrowseDepth then true else false)

    with

    = Table.AddColumn(HierarchyDepth, “IsLeaf”, each not List.Contains(HierarchyDepth[ParentKey], [NodeKey]))

    Reply

    • Hi FrankT,
      Are You sure about this formula? When I replace Imke’s code with your part I get expresion error “Cannot find column [ParentKey] in table”.

      Reply

  3. Great article, quick question I have a Manager Table with the ChildID and ParentID that is a guid D9665EF3-22D4-E611-80F8-5065F38AF871, there are 10K records, when I run the function it runs up to around 22K and then never finishes. I am wondering if the GUID is breaking the function?

    Reply

Leave a Reply