Forum Discussion
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)....
use Record.RemoveFields instead
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