Forum Discussion
Power Query - Recursive decomposition of rows
Hello Community Members,
I am trying to get some help with a scenario we are dealing with. We have daily holdings data and need a way to drill into the subsequent fund holdings to find % investment in the fund at security level. Data resides in one table see sample below
Account ID 123 has investement in Stock A and "MF A", AccountID joins to FundID to find out respective holdings of "MF A" and applies % investment into MF subsequent security. This needs to be shown as a union of Sec A + each security held in "MF B" and "MF C" because Account ID 123 is invested in "MF A" and "MF A" holds units of "MF B" which holds individual securities.
I was able to get the desired output by Mergeing the query using self join however when the semantic model is deployed the deomposed (merge) table gets too big because daily positions record count is over 140000 and decomposing results in over 2-3 million records per day. We have large semantic model enabled with incremental refresh set to retain last 24 months.
I looking to see if anyone had this problem and how to solve it with recursive query in Power BI or if there is a better way to semantic model storage.
| HoldingDate | AccountID | FundID | SecID | SecName | Qty | MV |
| 2025-01-01 | 123 | Sec A | Stock A | 10 | 100 | |
| 2025-01-01 | 123 | 996 | MF A | Mutual Fund A | 100 | 1000 |
| 2025-01-01 | 996 | 997 | MF B | Mutual Fund B | 50 | 5000 |
| 2025-01-01 | 996 | 998 | MF C | Mutual Fund C | 70 | 7000 |
| 2025-01-01 | 997 | Sec B | Stock B | 10 | 100 | |
| 2025-01-01 | 997 | Sec A | Stock A | 70 | 700 | |
| 2025-01-01 | 997 | Sec C | Stock C | 80 | 800 | |
| 2025-01-01 | 998 | Sec D | Stock D | 90 | 900 | |
| 2025-01-01 | 998 | Sec E | Stock E | 100 | 1000 | |
| 2025-01-01 | 998 | Sec F | Stock F | 110 | 1100 |
Hello Ira_27,
Can you please try this approach:
1. Create a Recursive Table
DecomposedHoldings = VAR FundHoldings = GENERATE( 'Holdings', FILTER( 'Holdings', 'Holdings'[FundID] = EARLIER('Holdings'[AccountID]) ) ) RETURN UNION('Holdings', FundHoldings)2. Calculate % Investment
% Investment = VAR TotalMV = CALCULATE( SUM('DecomposedHoldings'[MV]), ALL('DecomposedHoldings') ) RETURN DIVIDE(SUM('DecomposedHoldings'[MV]), TotalMV, 0)3. If the semantic model's size is an issue, filter the data in Power Query:
FilteredHoldings = Table.SelectRows( Source, each [HoldingDate] >= Date.AddMonths(DateTime.LocalNow(), -24) )Hope this helps!
3 Replies
Hello Ira_27,
Can you please try this approach:
1. Create a Recursive Table
DecomposedHoldings = VAR FundHoldings = GENERATE( 'Holdings', FILTER( 'Holdings', 'Holdings'[FundID] = EARLIER('Holdings'[AccountID]) ) ) RETURN UNION('Holdings', FundHoldings)2. Calculate % Investment
% Investment = VAR TotalMV = CALCULATE( SUM('DecomposedHoldings'[MV]), ALL('DecomposedHoldings') ) RETURN DIVIDE(SUM('DecomposedHoldings'[MV]), TotalMV, 0)3. If the semantic model's size is an issue, filter the data in Power Query:
FilteredHoldings = Table.SelectRows( Source, each [HoldingDate] >= Date.AddMonths(DateTime.LocalNow(), -24) )Hope this helps!
Thank you Sahir_Maharaj, i will try the approach and revert back here. In terms of semantic model size, the decomposed holding in prod is about 4Million rows per day. My incremental refresh is set to 24 months rolling so even loading one partition for decomposed table manually fails. I will try your method and see if that solves the issue.
I tried your approach but the decomposition only goes one level down. apparently i have atleast 2 levels to recurse over. Any thoughts on how to handle that?
