Forum Discussion

jaryszek's avatar
jaryszek
Super User
1 year ago
Solved

Recursive hierarchy in power query

Hello, my data is: I hve simple table like here: EmployeeID Name ManagerID 1 Alice NULL 2 Bob 1 3 Carol 1 4 Dave 2 5 Eve 2 6 Frank 3 and need to add Level1, and Level2 and Level3 colu...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    jaryszek Maybe try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMyUxOBdJKsTrRSkZAhlN+EpA0BPONgSznxKL8HLiICZDlklgG0mEEFjAFslyR+GZAlltRYl42kDZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, #"Name " = _t, ManagerID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"Name ", type text}, {"ManagerID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ManagerID"}, #"Changed Type", {"EmployeeID"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"ManagerID"}, {"Changed Type.ManagerID"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Changed Type", {"Changed Type.ManagerID"}, #"Expanded Changed Type", {"EmployeeID"}, "Expanded Changed Type", JoinKind.LeftOuter),
    #"Expanded Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Changed Type", {"ManagerID"}, {"Expanded Changed Type.ManagerID"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Expanded Changed Type", "Level", each if [ManagerID] = null and [Changed Type.ManagerID] = null then 1 else if [Changed Type.ManagerID] = null then 2 else 3),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Changed Type.ManagerID", "Expanded Changed Type.ManagerID"})
in
    #"Removed Columns"
Greg_Deckler's avatar
Greg_Deckler
Community Champion
1 year ago

jaryszek Maybe try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMyUxOBdJKsTrRSkZAhlN+EpA0BPONgSznxKL8HLiICZDlklgG0mEEFjAFslyR+GZAlltRYl42kDZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, #"Name " = _t, ManagerID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"Name ", type text}, {"ManagerID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ManagerID"}, #"Changed Type", {"EmployeeID"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"ManagerID"}, {"Changed Type.ManagerID"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Changed Type", {"Changed Type.ManagerID"}, #"Expanded Changed Type", {"EmployeeID"}, "Expanded Changed Type", JoinKind.LeftOuter),
    #"Expanded Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Changed Type", {"ManagerID"}, {"Expanded Changed Type.ManagerID"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Expanded Changed Type", "Level", each if [ManagerID] = null and [Changed Type.ManagerID] = null then 1 else if [Changed Type.ManagerID] = null then 2 else 3),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Changed Type.ManagerID", "Expanded Changed Type.ManagerID"})
in
    #"Removed Columns"