how to dynamically detangle parent-child tables in power query.
introduction.
Parent-child tables are a common way to store hierarchical data. However, using these tables “as is” does not work well in Power BI. With a few simple tricks, as the awesome guys from DAX Patterns described here, you can make the hierarchies work beautifully. In fact, the linked article from DAX Patterns was the inspirational foundation for this post. I will refer to it a few times throughout this article. That’s why I highly recommend to read through it as it will tremendously help you understand the suggested solution.
DAX Patterns’ way involves creating a depth column that is then used to unflatten/detangle the hierarchy into one column per hierarchy level. Both the depth as well as the hierarchy level columns are all calculated columns using DAX. For each hierarchy level you have to create a new calculated column manually. This implies, if in the future a new level would appear in the parent-child table, you have to create that column by hand – provided that you know about this new hierarchy level in the first place. Besides using Power Query instead of DAX as per Roche’s Maxim of Data Transformation, this blog post provides a dynamic way detangling parent-child tables into a similar shape suggested by DAX Patterns. Depending on the number of levels in the parent-child table, all levels will automatically be added as columns. The Power Query functions provided in this article are generic and should work for your use case, too. One caveat is that the M code is quite complex using loops which might be discouraging its implementation.
prerequisites.
1. A parent-child table
2. Power BI Desktop
plan of action.
1. What’s the goal?
2. What’s our starting point?
3. One attribute of interest: The ID used for the self-joins
4. Adding another attribute to our detangled table
5. Adding two or more attributes to our detangled table
6. Concatenating multiple attributes
7. Showtime: Adding and removing levels from parent-child table
1. What’s the goal?
The goal is to transform the parent-child table from this:
To this:
The special thing about the solution here is, the code calculates all hierarchy levels dynamically. This means, we do not need to create each new level by manually adding self-joins. Further, new hierarchy level columns will automatically appear during refresh.
2. What’s our starting point?
Let’s quickly recap the prerequisites. For the example in this post we are using the following organizational chart:
Here, the underlaying parent-child table:
| EmployeeID | Name | Title | EmployeeID_Manager |
| 1 | Maria | CEO | |
| 2 | Andy | Head of Marketing | 1 |
| 3 | Anna | Head of Finance | 1 |
| 4 | John | Head of Accounting | 1 |
| 5 | Mary | Head of Operations | 1 |
| 6 | Steven | Team Manager Ads | 2 |
| 7 | Hanna | Team Manager Market | 2 |
| 8 | Alex | Team Manager Control | 3 |
| 9 | Sarah | Team Manager Prod 1 | 5 |
| 10 | Adam | Team Manager Prod 2 | 5 |
| 11 | Jenny | Team Manager Prod 3 | 5 |
| 12 | Patrick | Ad Agent | 6 |
| 13 | Katie | Ad Agent | 6 |
| 14 | Michael | Ad Agent | 6 |
| 15 | Beth | Digital Market Special | 7 |
| 16 | Jonas | Controller | 8 |
| 17 | Julia | Controller | 8 |
| 18 | Chris | Accountant | 4 |
| 19 | Emma | Production Worker | 9 |
| 20 | Will | Production Worker | 9 |
| 21 | Therese | Production Worker | 10 |
| 22 | Trevor | Production Worker | 11 |
| 23 | Maria | Production Worker | 11 |
| 24 | Neil | Production Worker | 11 |
| 25 | Lisa | Intern | 22 |
3. One attribute of interest: The ID used for the self-joins
This is the standard case: We have one attribute of interest, in our example the EmployeeID. This case is only worthwhile when the IDs used for the iterative self-joins also provide analytical value to the end user. We start with the parent-child table called organisation parent-child.
Then, we select New Source and add a Blank Query:
Click on Advanced Editor, paste the subsequent M code and rename the function to root_function. The root function acts as the foundation for the looping (similar as the anchor in recursive CTE in SQL) and automatically triggers the loop function (see below).
let
Source = (ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text) =>
let
loop = 1,
level_string = "_Level_" & Number.ToText(loop),
PCTOnlyHighestLevel =
Table.DuplicateColumn(
Table.AddColumn(
Table.SelectColumns(
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) = null
),
{PCTChildColumn}
),
"Depth", each 1
),
PCTChildColumn, PCTParentColumn & level_string
),
PCTWithoutHighestLevel =
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) <> null
),
output =
@loop_function(PCTOnlyHighestLevel, PCTParentColumn & level_string, PCTWithoutHighestLevel, PCTChildColumn, PCTParentColumn, loop)
in
output
in
Source
After, create another function called loop_function in the same way as above.
let
Source = (BaseTable as table, BTCurrLevelColumn as text, ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, loop as number) =>
let
loop = loop + 1,
level_string = "_Level_" & Number.ToText(loop),
Table =
Table.ReplaceValue(
Table.ReplaceValue(
Table.RenameColumns(
Table.ExpandTableColumn(
Table.NestedJoin(
BaseTable, {BTCurrLevelColumn},
ParentChildTable, {PCTParentColumn},
level_string,
JoinKind.LeftOuter
),
level_string,
{PCTChildColumn},
{PCTChildColumn & level_string}
),
{{PCTChildColumn & level_string, PCTParentColumn & level_string}}
),
each Record.Field(_, PCTChildColumn), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTChildColumn) else Record.Field(_, PCTParentColumn & level_string ), Replacer.ReplaceValue,{PCTChildColumn}
),
each [Depth], each if Record.Field(_, PCTParentColumn & level_string ) = null then [Depth] else [Depth] + 1, Replacer.ReplaceValue,{"Depth"}
),
number_of_parents = List.NonNullCount(Table.Column(Table, PCTParentColumn & level_string)),
output =
if number_of_parents > 0
then @loop_function(Table, PCTParentColumn & level_string, ParentChildTable, PCTChildColumn, PCTParentColumn, loop as number)
else Table.TransformColumnTypes(BaseTable,{{"Depth", Int64.Type}})
in
output
in
Source
Next, select the root_function and invoke it with the following parameters:
As you can see, the functions are generic enough that you could reuse them for other parent child tables. Just select the table, the child column and the parent column and you are good to go.
And the following the result. The functions detangled the parent-child table into totally five levels of managers / parents. Once again, the levels are created dynamically meaning a new column will be added in case a new level appears during a refresh in the future.
Now, we can go back to the Power BI report and create a hierarchy on the level columns before hiding each one of the single columns. In fact, you could even exclude the EmployeeID from the hierarchy.
And here the result:
Obviously, just using the numbers makes it quite hard to understand what’s going on (see next bullet for using multiple attributes). Still, we can spot the blank rows in the matrix which is not very nice. The guys from DAX Patterns, of course, have a solution for this: We create two measures helping us hiding these blank rows:
EntityRowDepth = MAX ( 'organisation'[Depth] )
EntityBrowseDepth =
ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_1] )
+ ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_2] )
+ ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_3] )
+ ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_4] )
+ ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_5] )
Adding these two measures to the matrix gives us a better understanding on how they are going to help us:
Now it’s clear: When EntityBrowseDepth is greater than EntityRowDepth, we want to hide the row. So, we could consolidate the two measures, like below:
ShowRowEntityHelpMeasure =
IF
(
(
ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_1] ) +
ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_2] ) +
ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_3] ) +
ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_4] ) +
ISINSCOPE ( 'organisation'[EmployeeID_Manager_Level_5] )
) <= MAX ( 'organisation'[Depth] ),
"",
BLANK()
)
The BLANK() does the trick as the visual ignores them:
Note, here we are just interested in the hierarchy itself. If you’d like to to use it with other measures, it’s probably smartest to go with the approach and measures shown by DAX Patterns. In that case, you would embed the ShowRowEntityMeasure in an if statement together with the actual measure.
Now, we could even change the visual to a decomposition tree to illustrate the hierarchy:
4. Adding another attribute to our detangled table
As seen before, just using the ID column, does not really provide a proper analytical use case. Therefore, in this paragraph, we will add another attribute of interest: Name. The steps will be very similar. We start with the same to be detangled parent-child table:
Then, hit New Source and add a Blank Query:
Now, click on Advanced Editor, paste the subsequent M code and rename the function to root_function 2:
let
Source = (ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, PCTAddAttr1 as text) =>
let
loop = 1,
level_string = "_Level_" & Number.ToText(loop),
PCTOnlyHighestLevel =
Table.DuplicateColumn(
Table.DuplicateColumn(
Table.AddColumn(
Table.SelectColumns(
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) = null
),
{PCTChildColumn, PCTAddAttr1}
),
"Depth", each 1
),
PCTChildColumn, PCTParentColumn & level_string
),
PCTAddAttr1, PCTAddAttr1 & level_string
),
PCTWithoutHighestLevel =
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) <> null
),
output =
@#"loop_function 2"(PCTOnlyHighestLevel, PCTParentColumn & level_string, PCTWithoutHighestLevel, PCTChildColumn, PCTParentColumn, loop, PCTAddAttr1)
in
output
in
Source
After, create another function called loop_function 2 in the same way as above:
let
Source = (BaseTable as table, BTCurrLevelColumn as text, ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, loop as number, PCTAddAttr1 as text) =>
let
loop = loop + 1,
level_string = "_Level_" & Number.ToText(loop),
Table =
Table.ReplaceValue(
Table.ReplaceValue(
Table.ReplaceValue(
Table.RenameColumns(
Table.ExpandTableColumn(
Table.NestedJoin(
BaseTable, {BTCurrLevelColumn},
ParentChildTable, {PCTParentColumn},
level_string,
JoinKind.LeftOuter
),
level_string,
{PCTChildColumn, PCTAddAttr1},
{PCTChildColumn & level_string, PCTAddAttr1 & level_string}
),
{{PCTChildColumn & level_string, PCTParentColumn & level_string}}
),
each Record.Field(_, PCTChildColumn), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTChildColumn) else Record.Field(_, PCTParentColumn & level_string ), Replacer.ReplaceValue,{PCTChildColumn}
),
each Record.Field(_, PCTAddAttr1), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTAddAttr1) else Record.Field(_, PCTAddAttr1 & level_string ), Replacer.ReplaceValue,{PCTAddAttr1}
),
each [Depth], each if Record.Field(_, PCTParentColumn & level_string ) = null then [Depth] else [Depth] + 1, Replacer.ReplaceValue,{"Depth"}
),
number_of_parents = List.NonNullCount(Table.Column(Table, PCTParentColumn & level_string)),
output =
if number_of_parents > 0
then @#"loop_function 2"(Table, PCTParentColumn & level_string, ParentChildTable, PCTChildColumn, PCTParentColumn, loop as number, PCTAddAttr1)
else Table.TransformColumnTypes(BaseTable,{{"Depth", Int64.Type}})
in
output
in
Source
Now, select the root_function 2 and invoke it with the following parameters:
And here the result. This time, the function unwrapped the parent-child table into five levels of manager IDs plus their corresponding name. Again, the number of levels is built dynamically. Note, the picture below is cut off to the right.
Following the same steps as before (that is adding the measures from DAX Patterns), we can create a more meaningful hierarchy on the name attribute:
And also, the decomposition tree is way easier to understand:
5. Adding two or more attributes to our detangled table
Now that we know the names, we might wanna add another attribute to the detangled table, i.e. each employee’s role title. Of course, you can add even more attributes, but keep in mind that the table can get very wide. We added some colouring onto the code below so you can spot the difference between one attribute vs two attributes. The steps will be the same in case you even wanna add a third or fourth attribute.
Here the code for the root function that we call root_function 3:
let
Source = (ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, PCTAddAttr1 as text, PCTAddAttr2 as text) =>
let
loop = 1,
level_string = "_Level_" & Number.ToText(loop),
PCTOnlyHighestLevel =
Table.DuplicateColumn(
Table.DuplicateColumn(
Table.DuplicateColumn(
Table.AddColumn(
Table.SelectColumns(
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) = null
),
{PCTChildColumn, PCTAddAttr1, PCTAddAttr2}
),
"Depth", each 1
),
PCTChildColumn, PCTParentColumn & level_string
),
PCTAddAttr1, PCTAddAttr1 & level_string
),
PCTAddAttr2, PCTAddAttr2 & level_string
),
PCTWithoutHighestLevel =
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) <> null
),
output =
@#"loop_function 3"(PCTOnlyHighestLevel, PCTParentColumn & level_string, PCTWithoutHighestLevel, PCTChildColumn, PCTParentColumn, loop, PCTAddAttr1, PCTAddAttr2)
in
output
in
Source
And loop_function 3:
let
Source = (BaseTable as table, BTCurrLevelColumn as text, ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, loop as number, PCTAddAttr1 as text, PCTAddAttr2 as text) =>
let
loop = loop + 1,
level_string = "_Level_" & Number.ToText(loop),
Table =
Table.ReplaceValue(
Table.ReplaceValue(
Table.ReplaceValue(
Table.ReplaceValue(
Table.RenameColumns(
Table.ExpandTableColumn(
Table.NestedJoin(
BaseTable, {BTCurrLevelColumn},
ParentChildTable, {PCTParentColumn},
level_string,
JoinKind.LeftOuter
),
level_string,
{PCTChildColumn, PCTAddAttr1, PCTAddAttr2},
{PCTChildColumn & level_string, PCTAddAttr1 & level_string, PCTAddAttr2 & level_string}
),
{{PCTChildColumn & level_string, PCTParentColumn & level_string}}
),
each Record.Field(_, PCTChildColumn), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTChildColumn) else Record.Field(_, PCTParentColumn & level_string ), Replacer.ReplaceValue,{PCTChildColumn}
),
each Record.Field(_, PCTAddAttr1), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTAddAttr1) else Record.Field(_, PCTAddAttr1 & level_string ), Replacer.ReplaceValue,{PCTAddAttr1}
),
each Record.Field(_, PCTAddAttr2), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTAddAttr2) else Record.Field(_, PCTAddAttr2 & level_string ), Replacer.ReplaceValue,{PCTAddAttr2}
),
each [Depth], each if Record.Field(_, PCTParentColumn & level_string ) = null then [Depth] else [Depth] + 1, Replacer.ReplaceValue,{"Depth"}
),
number_of_parents = List.NonNullCount(Table.Column(Table, PCTParentColumn & level_string)),
output =
if number_of_parents > 0
then @#"loop_function 3"(Table, PCTParentColumn & level_string, ParentChildTable, PCTChildColumn, PCTParentColumn, loop as number, PCTAddAttr1, PCTAddAttr2)
else Table.TransformColumnTypes(BaseTable,{{"Depth", Int64.Type}})
in
output
in
Source
The hierarchy would look like the following:
And here the composition tree:
6. Concatenating multiple attributes
As we could see in the previous paragraph, adding multiple attributes to the table not only makes it very wide, but also comes along with challenges when trying to use the hierarchy for analysis. That’s why it might be smarter to do some concatenation where we utilise the data of interest but in a more simple way:
Here the code for the root function that we call root_function 4:
let
Source = (ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, PCTAddAttr1 as text, PCTAddAttr2 as text) =>
let
loop = 1,
level_string = "_Level_" & Number.ToText(loop),
PCTOnlyHighestLevel =
Table.CombineColumns(
Table.DuplicateColumn(
Table.DuplicateColumn(
Table.DuplicateColumn(
Table.AddColumn(
Table.SelectColumns(
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) = null
),
{PCTChildColumn, PCTAddAttr1, PCTAddAttr2}
),
"Depth", each 1
),
PCTChildColumn, PCTParentColumn & level_string
),
PCTAddAttr1, PCTAddAttr1 & level_string
),
PCTAddAttr2, PCTAddAttr2 & level_string
),
{PCTAddAttr1 & level_string, PCTAddAttr2 & level_string},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),PCTAddAttr1 & "_" & PCTAddAttr2 & level_string
),
PCTWithoutHighestLevel =
Table.SelectRows(
ParentChildTable,
each Record.Field(_, PCTParentColumn) <> null
),
output =
@#"loop_function 4"(PCTOnlyHighestLevel, PCTParentColumn & level_string, PCTWithoutHighestLevel, PCTChildColumn, PCTParentColumn, loop, PCTAddAttr1, PCTAddAttr2)
in
output
in
Source
And loop_function 4:
let
Source = (BaseTable as table, BTCurrLevelColumn as text, ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, loop as number, PCTAddAttr1 as text, PCTAddAttr2 as text) =>
let
loop = loop + 1,
level_string = "_Level_" & Number.ToText(loop),
Table =
Table.ReplaceValue(
Table.CombineColumns(
Table.ReplaceValue(
Table.ReplaceValue(
Table.ReplaceValue(
Table.ReplaceValue(
Table.RenameColumns(
Table.ExpandTableColumn(
Table.NestedJoin(
BaseTable, {BTCurrLevelColumn},
ParentChildTable, {PCTParentColumn},
level_string,
JoinKind.LeftOuter
),
level_string,
{PCTChildColumn, PCTAddAttr1, PCTAddAttr2},
{PCTChildColumn & level_string, PCTAddAttr1 & level_string, PCTAddAttr2 & level_string}
),
{{PCTChildColumn & level_string, PCTParentColumn & level_string}}
),
each Record.Field(_, PCTChildColumn), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTChildColumn) else Record.Field(_, PCTParentColumn & level_string ), Replacer.ReplaceValue,{PCTChildColumn}
),
each Record.Field(_, PCTAddAttr1), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTAddAttr1) else Record.Field(_, PCTAddAttr1 & level_string ), Replacer.ReplaceValue,{PCTAddAttr1}
),
each Record.Field(_, PCTAddAttr2), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTAddAttr2) else Record.Field(_, PCTAddAttr2 & level_string ), Replacer.ReplaceValue,{PCTAddAttr2}
),
each [Depth], each if Record.Field(_, PCTParentColumn & level_string ) = null then [Depth] else [Depth] + 1, Replacer.ReplaceValue,{"Depth"}
),
{PCTAddAttr1 & level_string, PCTAddAttr2 & level_string},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),PCTAddAttr1 & "_" & PCTAddAttr2 & level_string
),
each " - ",null,Replacer.ReplaceValue, {PCTAddAttr1 & "_" & PCTAddAttr2 & level_string}
),
number_of_parents = List.NonNullCount(Table.Column(Table, PCTParentColumn & level_string)),
output =
if number_of_parents > 0
then @#"loop_function 4"(Table, PCTParentColumn & level_string, ParentChildTable, PCTChildColumn, PCTParentColumn, loop as number, PCTAddAttr1, PCTAddAttr2)
else Table.TransformColumnTypes(BaseTable,{{"Depth", Int64.Type}})
in
output
in
Source
The hierarchy in a matrix:
The composition tree:
7. Showtime: Adding and removing levels from parent-child table
For this part we look into the very last organisation table we created. It currently looks like the following:
Now, we add a new attribute to our excel organisation table: We give Trevor – Production Worker another intern called Luke:
After we click the refresh button, Luke pops up in our composition tree:
Next, we wanna add another level to our organisation table. We hypothetically give Lisa – Intern another Intern called Sally:
After the refresh, unfortunately, nothing changes in the graph, but we can see in the Data pane that two new columns have appeared: EmployeeID_Manager_Level_6 and Name_Title_Level_6.
That this happens automatically can be a valuable advantage as you do not depend on the business telling you in advance about the hierarchy change. Of course, you still need to spot the change in Power BI, but the degree of reactiveness should still be reduced. Also, you are neither depending on your data warehouse team that needs to self-join the table once more in the DWH in order to to provide you with the new level. Instead, the column appears by itself meaning it is just on the Power BI developer to add the column to the hierarchy and align the ShowRowEntityHelpMeasure.
But what happens, if we remove Sally again from our organization table after we have added the sixth level to the hierarchy and the measure?
Expectedly, the hard dependencies we created broke: Both the measure as well as the hierarchy are missing the sixth level. This is, however, nothing too much to worry about as we can interpret the error message as a notification that the hierarchy has changed. Now we can just go and fix it.
You could, however, increase robustness, by specifying the minimum number of levels in the loop function:
let
Source = (BaseTable as table, BTCurrLevelColumn as text, ParentChildTable as table, PCTChildColumn as text, PCTParentColumn as text, loop as number, PCTAddAttr1 as text, PCTAddAttr2 as text) =>
let
loop = loop + 1,
level_string = "_Level_" & Number.ToText(loop),
Table =
Table.ReplaceValue(
Table.CombineColumns(
Table.ReplaceValue(
Table.ReplaceValue(
Table.ReplaceValue(
Table.ReplaceValue(
Table.RenameColumns(
Table.ExpandTableColumn(
Table.NestedJoin(
BaseTable, {BTCurrLevelColumn},
ParentChildTable, {PCTParentColumn},
level_string,
JoinKind.LeftOuter
),
level_string,
{PCTChildColumn, PCTAddAttr1, PCTAddAttr2},
{PCTChildColumn & level_string, PCTAddAttr1 & level_string, PCTAddAttr2 & level_string}
),
{{PCTChildColumn & level_string, PCTParentColumn & level_string}}
),
each Record.Field(_, PCTChildColumn), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTChildColumn) else Record.Field(_, PCTParentColumn & level_string ), Replacer.ReplaceValue,{PCTChildColumn}
),
each Record.Field(_, PCTAddAttr1), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTAddAttr1) else Record.Field(_, PCTAddAttr1 & level_string ), Replacer.ReplaceValue,{PCTAddAttr1}
),
each Record.Field(_, PCTAddAttr2), each if Record.Field(_, PCTParentColumn & level_string ) = null then Record.Field(_, PCTAddAttr2) else Record.Field(_, PCTAddAttr2 & level_string ), Replacer.ReplaceValue,{PCTAddAttr2}
),
each [Depth], each if Record.Field(_, PCTParentColumn & level_string ) = null then [Depth] else [Depth] + 1, Replacer.ReplaceValue,{"Depth"}
),
{PCTAddAttr1 & level_string, PCTAddAttr2 & level_string},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),PCTAddAttr1 & "_" & PCTAddAttr2 & level_string
),
each " - ",null,Replacer.ReplaceValue, {PCTAddAttr1 & "_" & PCTAddAttr2 & level_string}
),
number_of_parents = List.NonNullCount(Table.Column(Table, PCTParentColumn & level_string)),
output =
if number_of_parents > 0 or loop <= 6
then @#"loop_function 4"(Table, PCTParentColumn & level_string, ParentChildTable, PCTChildColumn, PCTParentColumn, loop as number, PCTAddAttr1, PCTAddAttr2)
else Table.TransformColumnTypes(BaseTable,{{"Depth", Int64.Type}})
in
output
in
Source
With the “or loop <= 6” you force the function to always create six hierarchy levels. It does not matter whether there is a sixth level in the parent-child table or not. If there is none, the level_6 columns will just contain nulls. This also means, you could simply create a bunch of additional levels ending up with several buffer or placeholders columns thereby increasing the robustness of your solution. This might be extra helpful, if your parent child table constantly changes.
end.
Obviously, the approach here works best with parent-child tables where a parent can have none, one or many children, and at the same time, a child only can have one and only one parent. When we happen to have an internal many-to-many relationship between parent and children, we need to be a bit more cautious. We might look into this in a future blog post.
Despite the hacky nature of the code, the solution hopefully helps you in dealing with parent child tables pulling the pressure from DAX and instead letting Power Query deal with it. There still is the design choice to make whether to leave the number of levels totally dynamic and risking to break dependencies when levels disappear or to have a a safety net by creating additional fixed placeholder/buffer level columns. Generally, this solution should provide you with less of an overhead when levels change since the need to adapt the levels upstream such as in a data warehouse or lakehouse is not needed. Instead, the issue lands straight on the report developer side. Worth mentioning, due to its dynamic nature, there are no real data contracts as we pivot the parent child table into a table with a flexible number of columns.