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:

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

Edit 2017-May-28: New files for download correcting error in “IsLeaf” (thx to FrankT in comments)

Enjoy & stay queryious ๐Ÿ˜‰

 

Comments (15) 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

      • I’ve uploaded new files that incorporate Franks corrections – working fine ๐Ÿ˜‰
        Thanks again FrankT!

        Reply

        • Hi Partnercenter,
          I’m very sorry – just recognized that there were still issues with “own” columnnames who caused the trouble. I’ve updated the files again (now also with blanks instead of parent dups).
          Pls let me know if you still run into trouble with it.
          Thx!

          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

  4. Thanks for this Imke. This came in very handy recently as my team had to bypass connecting Power BI to a cube and go straight to the underlying tables in SQL. Question: is there a reason why you have the child levels that dont have children filled in with the parent name, instead of just blank?

    Reply

    • Thx David, very pleased to hear!
      No reason for that other than following one of the examples from the referenced article. If you want to keep them blank, replace the “PivotTable”-step with this code (much easier actually):
      PivotTable = Table.AddColumn(HierarchyPath, “PivotTable”, each Table.AddIndexColumn(Table.FromColumns({[PathItems]}), “Level”,1,1)),

      Reply

Leave a Reply