Relations within same table

[Corstiaan]

Hey,

Use power bi for a year now. Now I run into the following problem, which I would like some advice on.

To create a dashboard for different departments with teams I have this table:

ID	Name	ID_OV
10	Finance	Null
20	Advice	10
30	Administration	10
40	HRM	null
50	Advice	40
60	Advice –P	50
70	Advice – M	50

To make a hierarchy in power bi, I feel like I will eventually need the following table:

ID	Level_0_Name	Level_0_Id	Level_1_Name	Level_1_Id	Level_2_Name	Level_2_Id
10	Finance	10	Null
20	Finance	10	Advice	20
30	Finance	10	Administration	30
40	HRM	40	null
50	HRM	40	Advice	50
60	HRM	40	Advice	50	Advice –P	60
70	HRM	40	Advice	50	Advice –M	70

Has anyone ever run into this problem? And how did you resolve it in the end? With power Query or Python?

Let me know what your advice or solution is.

[Corstiaan]

Thank you all very much. I have tried them all and they all give the right result!

Greetings Corstiaan

[Anonymous]

try also this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFyy8xLzEtOBbKUYnWilYxAYo4pZZlgIaAKkKAxRDA3My+zuKQosSQzPw8haQKS9AjyhZlgimKCCUSRGUJQ4VHDZIUAINcUImWOJuULlYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, ID_OV = _t]),
    ct = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"ID_OV", Int64.Type}}),

    getLev=(id)=> if  ct{[ID=id]}[ID_OV]=null then 0 else 1+@getLev(ct{[ID=id]}[ID_OV]),

    getRec=(id, lev)=>
    let 
    rec=ct{[ID=id]}[[Name],[ID]],
    names=Record.FieldNames(rec)
    in if ct{[ID=id]}[ID_OV]=null then Record.RenameFields(rec, {{names{0},"Name"& Text.From(lev)},{names{1},"ID" & Text.From(lev)}}) else  Record.RenameFields(rec, {{names{0},"Name"& Text.From(lev)},{names{1},"ID" & Text.From(lev)}}) & @getRec(ct{[ID=id]}[ID_OV], lev-1),
    
    tac=Table.AddColumn(ct, "exp",each  getRec([ID],getLev([ID]))),
    #"Removed Other Columns" = Table.SelectColumns(tac,{"exp"})
in
    #"Removed Other Columns"

after you request to expand the records list, you get this:

before push OK  click the Load More button to get somethink like this

[Anonymous · Accepted solution]

it is not very clear which elements establish the hierarchy, but I understand ID_OV refers to the ID of the higher hierarchy level.
If so, this might be for you.
It can also be improved with a little make up

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFyy8xLzEtOBbKUYnWilYxAYo4pZZlgIaAKkKAxRDA3My+zuKQosSQzPw8haQKS9AjyhZlgimKCCUSRGUJQ4VHDZIUAINcUImWOJuULlYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, ID_OV = _t]),
    ct = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"ID_OV", Int64.Type}}),

    getRec=(id)=>
    let 
    rec=Record.FieldValues(ct{[ID=id]}[[ID],[Name]])
    in if ct{[ID=id]}[ID_OV]=null then rec else rec & @getRec(ct{[ID=id]}[ID_OV]) 

    in Table.FromColumns(List.Zip(Table.AddColumn(ct, "exp",each  List.Reverse(getRec([ID])))[exp]),{"a","b","c","d","e","f"})

here a version more general

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFyy8xLzEtOBbKUYnWilYxAYo4pZZlgIaAKkKAxRDA3My+zuKQosSQzPw8haQKS9AjyhZlgimKCCUSRGUJQ4VHDZIUAINcUImWOJuULlYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, ID_OV = _t]),
    ct = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"ID_OV", Int64.Type}}),

    getRec=(id)=>
    let 
    rec=Record.FieldValues(ct{[ID=id]}[[Name],[ID]])
    in if ct{[ID=id]}[ID_OV]=null then rec else  @getRec(ct{[ID=id]}[ID_OV]) & rec,
    
    cols=List.Zip(Table.AddColumn(ct, "exp",each  getRec([ID]))[exp]),
    ncols=List.Count(cols),
    names=List.Combine(List.Transform({0..ncols/2-1}, each {"Name_lev_"&Text.From(_),"Id_lev_"&Text.From(_)})) 
    in Table.ReplaceValue(Table.FromColumns(cols,names),null,"", Replacer.ReplaceValue, names)

[Bohumil_Uhrin]

nice 🙂

[Bohumil_Uhrin]

Hi @Corstiaan,

you can also try this code - it gives the same output as Xue Ding's code, but it doesnt rely on "-" to identify level2.

However, it only works with 3 levels of hierarchy (level 0, 1, 2), but can be further expanded to more levels, if needed, using the same logic. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFyy8xLzEtOBbLySnNylGJ1opWMQOKOKWWZYGGgKpCgMUQwNzMvs7ikKLEkMz8PIWkCkvQI8kU2xRTFFBOIQjOEoG4AkGkKETZHEvaFCscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, ID_OV = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"ID_OV", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Level_0_ID", each if [ID_OV] = null then [ID] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Level_0_ID"}),
    #"Level_0_Name"= Table.NestedJoin(#"Filled Down", {"Level_0_ID"}, #"Changed Type", {"ID"}, "Level_0_Name", JoinKind.LeftOuter),
    #"Added Index" = Table.AddIndexColumn(Level_0_Name, "Index", 0, 1, Int64.Type),
    #"Expanded Level_0_Name" = Table.ExpandTableColumn(#"Added Index", "Level_0_Name", {"Name"}, {"Level_0_Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Level_0_Name",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Level_0_Name", "Level_0_ID", "Name", "ID_OV"}),
    #"Merge1"= Table.NestedJoin(#"Reordered Columns", {"ID_OV"}, #"Changed Type", {"ID"}, "Merge1", JoinKind.LeftOuter),
    #"Added Index1" = Table.AddIndexColumn(Merge1, "Index", 0, 1, Int64.Type),
    #"Expanded Merge1" = Table.ExpandTableColumn(#"Added Index1", "Merge1", {"Name", "ID_OV"}, {"Name.1", "ID_OV.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Merge1",{"Index"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Removed Columns1", "Level_1_Name", each if [ID_OV] = null then null else if [ID_OV.1] = null then [Name] else [Name.1]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Level_1_ID", each if [ID_OV] = null then null else if [ID_OV.1] = null then [ID] else [ID_OV]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column2",{"Name.1", "ID_OV.1"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"ID", "Level_0_Name", "Level_0_ID", "Level_1_Name", "Level_1_ID", "Name", "ID_OV"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Reordered Columns1", "Level_2_Name", each if [Level_0_Name] = [Name] then null else if [Level_1_Name] = [Name] then null else [Name]),
    #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Level_2_ID", each if [Level_0_Name] = [Name] then null else if [Level_1_Name] = [Name] then null else [ID]),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Conditional Column4",{"Name", "ID_OV"})
in
    #"Removed Columns3"

Best Regards

Bohumil Uhrin

[v-xuding-msft]

Hi @Corstiaan ,

You could try the code. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFyy8xLzEtOBbL8SnNylGJ1opWMQOKOKWWZYGGgKpCgMUQwNzMvs7ikKLEkMz8PIWkCkvQI8gWSeTBTTFFMMYEoNEMIKjxqmBwA5JlCZMxRZRR8oVKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, ID_OV = _t]),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"ID_OV", Text.Lower, type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Lowercased Text", "Custom", each if [ID_OV] = "null" then [Name] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Filled Down", "Custom.4", each if [Custom] = [Name] then [ID] else null),
    #"Filled Down2" = Table.FillDown(#"Added Conditional Column3",{"Custom.4"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down2", "Custom.1", each if Text.Contains([Name], "–") then "Advice" else if [ID_OV] = "null" then "null" else [Name]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if not Text.Contains([Custom.1], "Advice") and [Custom.1] <> "null" then [ID] else if [Name]= "Advice" then [ID] else null),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column2",{"Custom.2"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down1", "Custom.3", each if [ID_OV] = "null" then null else [Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom.2"}),
    #"Added Conditional Column4" = Table.AddColumn(#"Removed Columns", "Custom.2", each if Text.Contains([Name], "–") then [Name] else null),
    #"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Custom.5", each if [Custom.2] = [Name] then [ID] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column5",{"Name", "ID_OV"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Level_0_Name"}, {"Custom.4", "Level_0_ID"}, {"Custom.1", "Level_1_Name"}, {"Custom.2", "Level_2_Name"}, {"Custom.3", "Level_1_ID"}, {"Custom.5", "Level_2_ID"}})
in
    #"Renamed Columns"