Better way to do a calculation on multi-criteria

[Anonymous]

Hi everyone,
 
I'd like to know what is the best way to do the below:
 
I've one table which will be my "convert table":
Material	UoM	Convert in pcs
18600-20	PC	1
18600-20	CON	1
18600-20	RET	0
18600-20	TRP	48
18600-40	PC	1
18600-40	CON	1
18600-40	RET	0
18600-40	TRP	48
18610-20	PC	1
18610-20	CON	1
 
I also have my "order table" on where all my orders are stored:
Delivery	Material	Delivery quantity	SU
862545418	621001-40	0	TRP
862545418	621001-40	1	TRP
862545435	82260-01	0	RET
862545435	82260-01	12	RET
862545581	621001-40	0	TRP
862545581	621001-40	2	TRP
862545613	73280-02	9	PC
862545616	73280-02	2	PC
862545623	41475-02	2	PC
862545623	48375-00	2	PC
862545624	41475-02	2	PC
862545624	48375-00	2	PC
862545663	42013-01	4.800	PC
862545667	42013-01	44	RET
862545672	73265-02	200	PC
862545672	73270-02	200	PC
862545672	73275-02	200	PC
 
So I need to convert all the quantities from my "order table" in PC. Easy, ok, here's the resul I obtain:
 
Material	Delivery quantity	SU	PC - RET - TRP.# to convert	Delivery quantity (in pcs)	Total de Delivery quantity (in pcs) pour Material
40975-01	37415	PC	1	37415	37415
40975-01	12	RET	50	600	600
40975-01	6	TRP	200	1200	1200
Which is correct, but , I'd like to see it for the material (removed the lines by SU):
Material	Total de Delivery quantity (in pcs) pour Material
40975-01	7486600
 
It does sum(Delivery Quantity) * sum(PC - RET - TRP.# to convert)
 
What's the best way to have my result ? By a calculate measure, relationship management, merge queries? How will you do from both tables ?
 
Big thank you by advance.
 
Kind Regards,
Greg

[Fowmy]

Hi,

Use Power Query to solve this type of scenario.

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Fowmy,

Thank's for your answer.

Which tool of Power Query will you use to solde this ?

Greg

[Fowmy]

@Anonymous 

Merge should work

[Fowmy]

@Anonymous 

Please mark my reply as solution if it helped you. 

[Anonymous]

Not quite. I had already done this for the example above and it didn't work.

Can you tell me more about it?

[Anonymous]

Hi @Anonymous ,

Whether the problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[Anonymous]

Hi @Anonymous ,

What's your expected result? Please check if the below screen shot is what you want. If yes, you can get my sample file for more details.

In addition, you can also refer the following blogs to achieve it.

Append vs. Merge in Power BI and Power Query

Combine or Append Data in Power BI / Power Query: Main Concepts

Top 3 Steps to Append Queries Using Power Query

Best Regards

Rena