Sep 16 2021 09:50 AM
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!
Sep 16 2021 10:21 AM
SolutionAssuming 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"
Sep 16 2021 12:35 PM
Sep 16 2021 03:01 PM
@TobyC84 , you are welcome, glad it helped
Sep 16 2021 10:21 AM
SolutionAssuming 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"