Hello,
I have a dataset where there is a column with an account number, a column that contains a single account per row which belongs to the same owner, balance and account type columns.
I would like to accumulate all the accounts that belong to a given owner and calculate the net balances of that owner.
Here is a quick illustration of the transformation I made in Excel:
The order of the accounts in the output is arbitrary.
------------------------------------------------------------------
I have tried this:
List.RemoveNulls(List.Distinct(List.Accumulate(ToLists, {}, (state,current)=> if current{0}=_{0} then List.Combine({state, {current{0},current{1}}}) else state)))
it kind of works, but there are problems which make me think I'm not using the correct approach -
1. It only maps identical accounts of 1 "main account" - could only match rows 8 and 9 from the picture
2. It's very slow - I would expect list transformations to be much faster. I am working with only around 5000 records in the source and I am waiting 20-30 seconds for the refresh. If I try to Text.Combine the accounts to get a list of strings, it takes around a minute. And that's only working with a list of lists that has been separated from the other columns in the actual table. If I try to convert it to records (which I have to do in order to put it in the table), it can take upwards of 5 minutes.
3. I am not sure how to calculate the cumulative balance, that is, if I should keep the accounts in a list and sum the balances of each account or combine them into a string, insert the string of all accounts into the source table as a new column and use the entire string as a categorical variable for grouping.
I will be very grateful for any kind of help.
Best,
Filip
@Mahesh0016 @First row of output belongs to person 1 and row 2 belongs to person 2. The "Identical" column means that the account number in column "Identical" is owned by the same person as "Account Nr"
