SOLVED

Power Query Replace Data Help Please (Tiime Sensitive)

%3CLINGO-SUB%20id%3D%22lingo-sub-2757060%22%20slang%3D%22en-US%22%3EPower%20Query%20Replace%20Data%20Help%20Please%20(Tiime%20Sensitive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2757060%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20everyone!%20Here%20is%20my%20conundrum.%20I%20have%20a%20workbook%20with%20several%20columns%20and%20rows.%20I%20have%20a%20column%20for%20product%20SKUs%20and%20a%20column%20for%20Price%2C%20as%20well%20as%20a%20bunch%20of%20other%20info%20in%20%22workbook%20A%22.%20I%20need%20to%20replace%20the%20prices%20in%20%22workbook%20A%22%20with%20the%20prices%20from%20%22workbook%20B%22%20only%20where%20the%20SKU%20matches.%20So%20I%20understand%20the%20basic%20logic%20of%20what%20I%20need%20to%20do%20%3D%20Replace(%22price%20from%20workbook%20A%22)%20with%20(%22price%20from%20workbook%20B%22)%20where%20(%22SKU%20from%20workbook%20A%22)%20%3D%3D%20(%22SKU%20from%20workbook%20B%22)%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20two%20workbooks%20don't%20have%20the%20same%20amount%20of%20rows%20either.%20So%2C%20I%20need%20to%20sort%20of%20iterate%20through%20them%2C%20I%20think%2C%20and%20compare%20each%20row%20in%20workbook%20A%20with%20the%20workbook%20B%20rows.%20That's%20because%20only%26nbsp%3B%20certain%20products%20have%20had%20price%20changes.%20If%20that%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20for%20the%20life%20of%20me%2C%20I%20can't%20sort%20it%20out%20and%20don't%20fully%20understand%20how%20the%20Power%20Query%20editor%20could%20accomplish%20this.%20This%20is%20Windows%20OS%2C%20btw.%20Your%20help%20is%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2757060%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2757247%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Replace%20Data%20Help%20Please%20(Tiime%20Sensitive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2757247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1158017%22%20target%3D%22_blank%22%3E%40TobyC84%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20SKU%20are%20unique%20in%20both%20tables%2C%20you%20may%20merge%20A%20with%20B%2C%20add%20custom%20column%20for%20corrected%20price%20(if%20null%20then%20oldPrice%20else%20newPrice)%20and%20do%20cosmetic%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20607px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310839iCD580D0E0BDFB518%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Egenerated%20script%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Table.NestedJoin(%0A%20%20%20%20%20%20%20%20TableA%2C%20%7B%22SKU%22%7D%2C%0A%20%20%20%20%20%20%20%20Table2%2C%20%7B%22SKU%22%7D%2C%0A%20%20%20%20%20%20%20%20%22Table2%22%2C%20JoinKind.LeftOuter%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Expanded%20Table2%22%20%3D%20Table.ExpandTableColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22Table2%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Price%22%7D%2C%20%7B%22Price.1%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20Table2%22%2C%0A%20%20%20%20%20%20%20%20%22Price%20Corrected%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20%5BPrice.1%5D%20%3D%20null%20%0A%20%20%20%20%20%20%20%20%20%20%20%20then%20%5BPrice%5D%20%0A%20%20%20%20%20%20%20%20%20%20%20%20else%20%5BPrice.1%5D)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%7B%22SKU%22%2C%20%22Price%20Corrected%22%2C%20%22V%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hey everyone! Here is my conundrum. I have a workbook with several columns and rows. I have a column for product SKUs and a column for Price, as well as a bunch of other info in "workbook A". I need to replace the prices in "workbook A" with the prices from "workbook B" only where the SKU matches. So I understand the basic logic of what I need to do = Replace("price from workbook A") with ("price from workbook B") where ("SKU from workbook A") == ("SKU from workbook B");

 

The two workbooks don't have the same amount of rows either. So, I need to sort of iterate through them, I think, and compare each row in workbook A with the workbook B rows. That's because only  certain products have had price changes. If that makes sense.

 

But for the life of me, I can't sort it out and don't fully understand how the Power Query editor could accomplish this. This is Windows OS, btw. Your help is greatly appreciated!

3 Replies
best response confirmed by TobyC84 (New Contributor)
Solution

@TobyC84 

Assuming SKU are unique in both tables, you may merge A with B, add custom column for corrected price (if null then oldPrice else newPrice) and do cosmetic

For such sample

image.png

generated script is

let
    Source = Table.NestedJoin(
        TableA, {"SKU"},
        Table2, {"SKU"},
        "Table2", JoinKind.LeftOuter
    ),
    #"Expanded Table2" = Table.ExpandTableColumn(
        Source,
        "Table2",
        {"Price"}, {"Price.1"}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Expanded Table2",
        "Price Corrected",
        each
            if [Price.1] = null 
            then [Price] 
            else [Price.1]),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Added Custom",
        {"SKU", "Price Corrected", "V"}
    )
in
    #"Removed Other Columns"
Sergei, thanks for the response! I was able to sort it out because of your direction! Thank you sir!

@TobyC84 , you are welcome, glad it helped