Power Query - Recursive decomposition of rows

[Ira_27]

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

[Ira_27]

Hi @Sahir_Maharaj 

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? 

[Ira_27]

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. 

[Sahir_Maharaj · Accepted solution]

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!