Power query Aggregate list.sum with a condition, using aggregate

[ckurir]

Hi

I am using "Merge" to combine/join two tables where I like to sum values from table 2.

Table1 has orderID (plus more):

ORDERID

Order1

Order2

Table2 is details for the order:

OrderIDCost	Valuetype	Value	Salesdoc
Order1	costA	100	abcd
Order1	costA	100
Order1	costA	100
Order1	costB	100
Order2	costB	150	xyz
Order2	CostA	250

Il ike to get this result after merging

OrderID	CostA	CostB	SalesDOK	....	....
order1	300	100	abcd
order2	250	150	xyz

If I use the build in fucntion to aggregate/expand columns, I can only sum over the entire "Value" field per orderid.

I like something like List.Sum([Value] where Valuetype="CostA" ..)

And place in colum "CostA" respectively column "CostB".

Plus at he same time get the "first non blank value" of SalesDOk in "SalesDOk" column.

I cannot get around it, any one know?
Is it just me, I think M is not that intuitive like SQL?

Christian

[Anonymous · Accepted solution]

try this

[ckurir]

Very nice, perfect. The pivot operation in 2nd table, is that a feature that I can access via the PQ interface or only via advance editor?

Christian

[Anonymous]

in this case is enough via GUI