Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
try this
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.