cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ckurir
Regular Visitor

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

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:

OrderIDCostValuetypeValueSalesdoc
Order1costA100abcd
Order1costA100 
Order1costA100 
Order1costB100 
Order2costB150xyz
Order2CostA250 

 

 

Il ike to get this result after merging

OrderIDCostACostBSalesDOK........
order1300100abcd  
order2250150xyz  

 

 

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

 

 

 

 

1 ACCEPTED SOLUTION
Rocco_sprmnt21
Super User II
Super User II

3 REPLIES 3
Rocco_sprmnt21
Super User II
Super User II

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

in this case is enough via GUI

 

image.png

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors