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 CodeAccount NameAmountCost CentreStaffBCDE446Salary1000AEmployee A 447Annual Leave100AEmployee A 449Superannuation100AEmployee A 446Salary2000BEmployee B 10001000 447Annual Leave200BEmployee B 100100 449Superannuation100BEmployee B 5050 446Salary2500CEmployee C 2000500447Annual Leave100CEmployee C 8020449Superannuation100CEmployee C 8020 Output:Cost CentreAccount CodeAccount NameAmountA446Salary1000A447Annual Leave100A449Superannuation100B446Salary0B447Annual Leave0B449Superannuation0C446Salary1000C447Annual Leave100C449Superannuation50D446Salary3000D447Annual Leave180D449Superannuation130E446Salary500E447Annual Leave20E449Superannuation20 Thank you in advance

use Record.RemoveFields instead

Frequent Visitor

2 years ago

Solved

Need help with Power Query, Suming with conditions

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

Need Help

AlienSx
2 years ago
use Record.RemoveFields instead

3 Replies

AlienSx

Super User

2 years ago

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
Frequent Visitor
2 years ago
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
  Super User
  2 years ago
  use Record.RemoveFields instead

Forum Discussion

Need help with Power Query, Suming with conditions

3 Replies

Related content

Conditional Column in power query

Power Query If condition

Suming Time

Conditional formatting for power query

Rank with conditions