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
Highlighted
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

@Sergei Baklan 

 

Thank you!

@dzhogov , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 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
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies