Power Query - Grouping Accounts of Same Ownership

[filipk99]

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

[filipk99]

@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"

[Mahesh0016]

@filipk99  can you please exeplain how did you separate account codes in output?