The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
18 | |
16 | |
13 |