Power Query Replace Data Help Please (Tiime Sensitive)

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!

excel

Formulas and Functions

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

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"

Correct Price.xlsx19 KB

3 Replies