Forum Discussion
Creating hierarchy tree from data
Hello, need help in figuring out how to manipulate data that is returned to me where the parent column may have both the top level and a sub level. So if the data looks like this:
| Parent | Name |
1 | A |
| 2 | B |
| 3 | C |
| 4 | 1 |
| 5 | 2 |
| 6 | 3 |
Trying to transform it to look like:
| Parent | Sub | Name |
| 4 | 1 | A |
| 5 | 2 | B |
| 6 | 3 | C |
Any suggestions? I'm this is probably a basic topic but wasn't able to find anything on it. Also, there are scenarios where the subs may be nested as well before it gets to the top level (10>9>7>D). Need to be able to account for that as well. Any help would be greatly appreciated! Thank you!
Hi dstanisljevic ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Added an auxiliary table to get column values without parent elements (4, 5, 6)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMgGyDMEsUyDLCMwyA7KMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(#"Changed Type"[Name], [Parent]) then null else [Parent]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Parent", "Name"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null)), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Custom", "Name"}}) in #"Renamed Columns"2. Append this table to the original table
3. Create some calculated columns to get the path and pathlength by using Parent and Child functions
Path = PATH('Table'[Name],'Table'[Parent])Pathlen = PATHLENGTH('Table'[Path])4. Create calculated columns to get the parent, sub and name values
nParent = VAR _len = MAX ( 'Table'[Pathlen] ) RETURN IF ( 'Table'[Pathlen] = _len, PATHITEM ( 'Table'[Path], 1 ), BLANK () )Sub = VAR _len = MAX ( 'Table'[Pathlen] ) RETURN IF ( 'Table'[Pathlen] = _len, PATHITEM ( 'Table'[Path], 2 ))nName = VAR _len = MAX ( 'Table'[Pathlen] ) RETURN IF ( 'Table'[Pathlen] = _len, PATHITEM ( 'Table'[Path], 3 ))Best Regards
2 Replies
Should also add that not all values in the 'Parent' column exist in the 'Name' column.
Hi dstanisljevic ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Added an auxiliary table to get column values without parent elements (4, 5, 6)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMgGyDMEsUyDLCMwyA7KMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(#"Changed Type"[Name], [Parent]) then null else [Parent]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Parent", "Name"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null)), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Custom", "Name"}}) in #"Renamed Columns"2. Append this table to the original table
3. Create some calculated columns to get the path and pathlength by using Parent and Child functions
Path = PATH('Table'[Name],'Table'[Parent])Pathlen = PATHLENGTH('Table'[Path])4. Create calculated columns to get the parent, sub and name values
nParent = VAR _len = MAX ( 'Table'[Pathlen] ) RETURN IF ( 'Table'[Pathlen] = _len, PATHITEM ( 'Table'[Path], 1 ), BLANK () )Sub = VAR _len = MAX ( 'Table'[Pathlen] ) RETURN IF ( 'Table'[Pathlen] = _len, PATHITEM ( 'Table'[Path], 2 ))nName = VAR _len = MAX ( 'Table'[Pathlen] ) RETURN IF ( 'Table'[Pathlen] = _len, PATHITEM ( 'Table'[Path], 3 ))Best Regards
