SOLVED
Home

Specific Power Query Merge help

%3CLINGO-SUB%20id%3D%22lingo-sub-414270%22%20slang%3D%22en-US%22%3ESpecific%20Power%20Query%20Merge%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-414270%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20a%20very%20particulate%20situation%20within%20Power%20Query%20which%20I%20am%20not%20sure%20how%20to%20go%20about.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20data%20source%20for%20a%20sales%20dashboard%2C%20I%20have%20a%20table%20within%20PQ%20where%20I%20have%20monthly%20sales%20to%205%20clients.%20Each%20row%20has%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMONTH%3B%20CLIENT%3B%20PRODUCT%3B%20QUANTITY%3B%20VALUE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20one%20client%20uses%20special%20discounts%20which%20no%20one%20else%20does%2C%20and%20so%20the%20VALUE%20column%20is%20empty.%20I%20have%20a%20sheet%20with%20PRICE%2FKG%20for%20the%20client%20(including%20discounts)%20so%20the%20solution%20is%20to%20multiply%20QUANTITY%20by%20this%20PRICE%5CKG%20to%20result%20in%20the%20correct%20VALUE.%20However%2C%20I%20am%20not%20sure%20how%20to%20go%20about%20merging%20the%20two%20tables%20so%20as%20to%20match%20only%20the%20CLIENT%20in%20question%20and%20do%20the%20actual%20multiplication.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-414270%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-414446%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20Power%20Query%20Merge%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-414446%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313240%22%20target%3D%22_blank%22%3E%40dzhogov%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20your%20logic%20correctly%20you%20may%20merge%20your%20main%20table%20with%20PRICE%2FKG%20on%20clients%20and%20add%20new%20column%20with%20condition%20if%20%5BVALUE%5D%3Dnull%20then%20%5BQUANTITY%5D*%5BPRICE%2FKG%5D%20else%20%5BVALUE%5D%2C%20after%20that%20remove%20VALUE%20and%20PRICE%2FKG%20columns%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417353%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20Power%20Query%20Merge%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417932%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20Power%20Query%20Merge%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313240%22%20target%3D%22_blank%22%3E%40dzhogov%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
dzhogov
Occasional Contributor

Hello, I have a very particulate situation within Power Query which I am not sure how to go about.

 

As a data source for a sales dashboard, I have a table within PQ where I have monthly sales to 5 clients. Each row has

 

MONTH; CLIENT; PRODUCT; QUANTITY; VALUE.

 

However, one client uses special discounts which no one else does, and so the VALUE column is empty. I have a sheet with PRICE/KG for the client (including discounts) so the solution is to multiply QUANTITY by this PRICE\KG to result in the correct VALUE. However, I am not sure how to go about merging the two tables so as to match only the CLIENT in question and do the actual multiplication. 

3 Replies
Solution

Hi @dzhogov ,

 

If I understood your logic correctly you may merge your main table with PRICE/KG on clients and add new column with condition if [VALUE]=null then [QUANTITY]*[PRICE/KG] else [VALUE], after that remove VALUE and PRICE/KG columns

Highlighted

@Sergei Baklan 

 

Thank you!

@dzhogov , you are welcome

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies