Need help with Power Query, Suming with conditions

[SaberSuM]

Hi,

Is there a anyway in power query to transform the sample data into the second table below. Basically, some employees need to split costs based on a ratio to other cost centres (B, C, D or E). Using staff B as an example, his costs should be split across cost centre C & D while no costs should remain at cost centre B.

Sample Data:

Account Code	Account Name	Amount	Cost Centre	Staff	B	C	D	E
446	Salary	1000	A	Employee A
447	Annual Leave	100	A	Employee A
449	Superannuation	100	A	Employee A
446	Salary	2000	B	Employee B		1000	1000
447	Annual Leave	200	B	Employee B		100	100
449	Superannuation	100	B	Employee B		50	50
446	Salary	2500	C	Employee C			2000	500
447	Annual Leave	100	C	Employee C			80	20
449	Superannuation	100	C	Employee C			80	20

Output:

Cost Centre	Account Code	Account Name	Amount
A	446	Salary	1000
A	447	Annual Leave	100
A	449	Superannuation	100
B	446	Salary	0
B	447	Annual Leave	0
B	449	Superannuation	0
C	446	Salary	1000
C	447	Annual Leave	100
C	449	Superannuation	50
D	446	Salary	3000
D	447	Annual Leave	180
D	449	Superannuation	130
E	446	Salary	500
E	447	Annual Leave	20
E	449	Superannuation	20

Thank you in advance

[AlienSx]

let
    Source = sample_data,
    upd_amount = Table.AddColumn(
        Source, "updated_amount", 
        each  - List.Sum(
            {-[Amount]} & 
                Record.FieldValues(
                    Record.SelectFields(
                        _, 
                        {"A", "B", "C", "D", "E"}, 
                        MissingField.Ignore
                    )
                )
        )
    )[[Account Code], [Account Name], [Cost Centre], [updated_amount]],
    shared_costs = Table.UnpivotOtherColumns(
        Table.RemoveColumns(Source, {"Cost Centre", "Staff", "Amount"}), 
        {"Account Code", "Account Name"}, 
        "Cost Centre", "updated_amount"
    ),
    output = Table.Group(
        upd_amount & shared_costs, 
        {"Account Code", "Account Name", "Cost Centre"}, 
        {{"Amount", each List.Sum([updated_amount])}}
    )
in
    output

[SaberSuM]

is there anyway to avoid listing out the cost centres. The full list of cost centres might change on a monthly basis, and it would require constant updates to the code.

Thank you!

[AlienSx · Accepted solution]

use Record.RemoveFields instead