SOLVED

Power Query Replace Data Help Please (Tiime Sensitive)

Copper 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 (Copper 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

1 best response

Accepted Solutions
best response confirmed by TobyC84 (Copper 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"

View solution in original post