Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power BI Community,
I am trying to flatten a Parent-Child Hiearchy table (with jumping levels) in Power Query and I would appreciate any tips.
The context: the parent-child hierarchy table is coming from a computerized maintenance management system.
The tricky part for me: jumping hierarchy levels. And tranforming the table in M instead of DAX.
This is a simplified version of the hierarchy with jumping levels.
What I want to achieve is:
AS_IS: Parent-Child Hiearchy table (The original table has ~ 300 K rows.)
Child key | Parent key | Hierarchy level |
Comp_A | Subunit_A | 6-COMPONENT |
Subunit_A | Unit_A | 5-SUBUNIT |
Unit_A | Equipment_A | 4-UNIT |
Equipment_A | ProdArea_A | 3-EQUIPMENT |
ProdArea_A | Plant_A | 2-PRODAREA |
Plant_A | 1-PLANT | |
Comp_B | Unit_B | 6-COMPONENT |
Unit_B | Equipment_B | 4-UNIT |
Equipment_B | ProdArea_B | 3-EQUIPMENT |
ProdArea_B | Plant_A | 2-PRODAREA |
TO-BE: Flattned Hierarchy table with conditionals
Child key | 1-PLANT | 2-PRODAREA | 3-EQUIPMENT | 4-UNIT | 5-SUBUNIT | 6-COMPONENT | Conditional explanation |
Comp_A | Plant_A | ProdArea_A | Equipment_A | Unit_A | Subunit_A | Comp_A | |
Subunit_A | Plant_A | ProdArea_A | Equipment_A | Unit_A | Subunit_A | ||
Unit_A | Plant_A | ProdArea_A | Equipment_A | Unit_A | |||
Equipment_A | Plant_A | ProdArea_A | Equipment_A | ||||
ProdArea_A | Plant_A | ProdArea_A | |||||
Plant_A | Plant_A | ||||||
Comp_B | Plant_A | ProdArea_B | Equipment_B | Unit_B | Unit_B | Comp_B | If there is a "jump" in levels, return the next parent key |
Unit_B | Plant_A | ProdArea_B | Equipment_B | Unit_B | |||
Equipment_B | Plant_A | ProdArea_B | Equipment_B | ||||
ProdArea_B | Plant_A | ProdArea_B |
I was able to flatten this table with DAX, using PATH, PAHTITEM, LOOKUPVALUE and SWITCH for each hiearchy level column. However, the target is to create the flattned table in dataflows and use it in different reports in Power BI. Therefore I need to achieve the same result with M syntax in Power Query.
There are no cases of multiple parents.
Since the case is fairly complicated, I would appreciate tips to reproduce the functions in M for PATH and PATHITEM. From there I can try to parse by a PATH column by delimiter and add the conditionals by column, I believe.
Solved! Go to Solution.
Hi, @juncr ;
You could try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLYh3VNJRCi5NKs3LLAGzzXSd/X0D/P1c/UKUYnWiUeRCYQxT3eBQp1A/T4gSuLBrYWlmQW5qHoRnogtXgSoRUJSf4liUmgjmGOu6BoZ6BvjC7EORDMhJhOox0g0I8ndxDHJ1hKiCSwCRoW6AjyNUO9hPTjC3OmHxEFwC4SgnXK51QnatEz7XOuF0bSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child key" = _t, #"Parent key" = _t, #"Hierarchy level" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Child key", type text}, {"Parent key", type text}, {"Hierarchy level", type text}}),
myfunction = (ChildCol,ParentCol,CurrentParent)=>
let
mylist=Table.Column(Table.SelectRows(#"Changed Type",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(#"Changed Type",
"Path", each
Text.Trim(
Text.Combine( List.Distinct(
List.Reverse(
List.RemoveItems(
Text.Split(myfunction("Child key","Parent key",[Child key]),"|"),{""}
)
)
&{[Parent key],[Child key]})
,
"|"
)
,"|"
)
)
in
Path
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @juncr ;
You could try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLYh3VNJRCi5NKs3LLAGzzXSd/X0D/P1c/UKUYnWiUeRCYQxT3eBQp1A/T4gSuLBrYWlmQW5qHoRnogtXgSoRUJSf4liUmgjmGOu6BoZ6BvjC7EORDMhJhOox0g0I8ndxDHJ1hKiCSwCRoW6AjyNUO9hPTjC3OmHxEFwC4SgnXK51QnatEz7XOuF0bSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child key" = _t, #"Parent key" = _t, #"Hierarchy level" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Child key", type text}, {"Parent key", type text}, {"Hierarchy level", type text}}),
myfunction = (ChildCol,ParentCol,CurrentParent)=>
let
mylist=Table.Column(Table.SelectRows(#"Changed Type",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(#"Changed Type",
"Path", each
Text.Trim(
Text.Combine( List.Distinct(
List.Reverse(
List.RemoveItems(
Text.Split(myfunction("Child key","Parent key",[Child key]),"|"),{""}
)
)
&{[Parent key],[Child key]})
,
"|"
)
,"|"
)
)
in
Path
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.