Creating hierarchy tree from data

[dstanisljevic]

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!

[dstanisljevic]

Should also add that not all values in the 'Parent' column exist in the 'Name' column.

[v-yiruan-msft · Accepted solution]

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