Forum Discussion
create multilevel heirarchy table from single table
Hello team, I have a table like
| Team ID | Team Name | Parent Team ID |
| 1 | A | null |
| 2 | B | null |
| 3 | C | 1 |
| 4 | D | 1 |
| 5 | E | 3 |
| 6 | F | 3 |
| 7 | G | 2 |
| 8 | H | 2 |
| 9 | I | 7 |
i load this table dynamically and from this I have to derive below logically ( Team A and B are highest level portfolio hence not appearing in the first column, team C and G are also parent of some team but not highest level hence appearning in the first level )
| Team | Parent Team | Portfolio |
| C | C | A |
| D | D | A |
| E | C | A |
| F | C | A |
| G | G | B |
| H | H | B |
| I | G | B |
Hope the requirement is clear with example , I could achieve 2 level(team and its parent) using same table with simple inner join (self) but getting output like above I am facing some or other challange.. can you please help with the power query for this. thanks in advane.
1 Reply
A very fundamental use case of recursive function,
let udf_Ancestor = (parent, ancestor) => let pos = List.PositionOf(IDs, parent) in if pos is null or pos=-1 then ancestor else @udf_Ancestor(#"Parent IDs"{pos}, parent), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pce5DYAwEAXRXn7sxDZgCLmhhtX23wYjS2ww0hszZSWtJE+mArZ/Ktgp9xvQETeik2q/CV1xDd1U+s3oiVvQS03uHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team ID" = _t, #"Team Name" = _t, #"Parent Team ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team ID", Int64.Type}, {"Team Name", type text}, {"Parent Team ID", Int64.Type}}), IDs = #"Changed Type"[Team ID], #"Parent IDs" = #"Changed Type"[Parent Team ID], #"Invoked udf_Ancestor" = Table.CombineColumns(#"Changed Type", {"Team ID","Parent Team ID"}, each udf_Ancestor(_{1}, _{0}), "Portfolio") in #"Invoked udf_Ancestor"
