Merge Query with Allocation by using the same selection of column from Left table

[LaurentZ]

Hi,

I've a DB which is very detailed (breakdown by Category, Customers and so many others).

I've fixed Costs on Total Category.

If I do a simple merge query, then I will have the same fixed cost on all lines, this is not what I want...

In Excel I would have managed it via a ratio [Turnover of the line / Sumifs(total Turnover, same Category)] * fixed cost. I must precise that I'm in Excel Power Query Only (not power BI) so I can't do it via DAX.

Is there any way to :

1. merge query with direct allocation of the cost based on criteria or
2. add a column to do what a sumifs would have done or
3. no other way than doing a "group by" and merge queries to have the total ?

Thank you in advance for your help.

Laurent

[Jimmy801 · Accepted solution]

Hello @LaurentZ 

to use Table.Group and Table.Join/NestedJoin is just fine and for sure the reccomended approach to take.

What should be wrong about that?

Jimmy

[LaurentZ]

Hi @Jimmy801,

First, thank you for your answer.

Well that's not a wrong way to do it, I was just wondering if there is another way to do it.

Ok, then I go with a groupby and merge.

Regards,

Laurent