Forum Discussion
Helper IHow to create hierarchy bases on column values
Have a table simialir to the following, and I would like to create a hierarchy based on the values. Each level is spaced (indent) different and it goes down like 10+ levels. Would like to be able to create slicer that can show everything assigned to Level 1, then see what within Level 1 is assigned to Level 1.1. Do need to go down to all levels, at least 4.
Code | Name |
| ABC.01 | T |
| ABC.01.01 | U |
| ABC.01.01.01 | V |
| ABC.02 | W |
| ABC.02.01 | X |
| ABC.02.01.01 | Y |
Hi DCS2011 ,
Please try:
First add two columns:
Then pivot your data:
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jMwVNJRClGK1YFxISKhqCIQwTCEoBGQG47EhSiIQBWBCEYqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Level", each Text.Length( Text.Select([Code],"."))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", each Text.Range([Code],Text.PositionOf([Code],".")+1, try Text.PositionOf([Code],".",Occurrence.All){1} -Text.PositionOf([Code],".")-1 otherwise Text.Length([Code]) -Text.PositionOf([Code],".")-1 )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Code"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Level", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Level", type text}}, "en-US")[Level]), "Level", "Name") in #"Pivoted Column"Then create a hierarchy:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-jianboli-msftCommunity Support
Hi DCS2011 ,
Please try:
First add two columns:
Then pivot your data:
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jMwVNJRClGK1YFxISKhqCIQwTCEoBGQG47EhSiIQBWBCEYqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Level", each Text.Length( Text.Select([Code],"."))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", each Text.Range([Code],Text.PositionOf([Code],".")+1, try Text.PositionOf([Code],".",Occurrence.All){1} -Text.PositionOf([Code],".")-1 otherwise Text.Length([Code]) -Text.PositionOf([Code],".")-1 )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Code"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Level", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Level", type text}}, "en-US")[Level]), "Level", "Name") in #"Pivoted Column"Then create a hierarchy:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.