Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Corstiaan
Regular Visitor

Relations within same table

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

 

 

image.png

 

 

 

 

View solution in original post

6 REPLIES 6
Corstiaan
Regular Visitor

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

Greetings Corstiaan

Anonymous
Not applicable

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:

 

image.png

 

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

 

image.png

 

 

 

 

Anonymous
Not applicable

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)

 

 

image.png

 

 

 

 

nice 🙂

Bohumil_Uhrin
Helper II
Helper II

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
Community Support
Community Support

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"

v-xuding-msft_0-1601288893281.png

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors