Use Power Query to change one column based on criteria in two columns.

Vaslo

Board Regular
Joined
Jun 3, 2009
Messages
159
Hello all,

I have an issue where I have to rename some customers based on some silly historic changes that were overwritten. In Power Query, I'd like to do the following:

1) Check two columns for a specific customer name and a specific location.
2) Based on criteria in both, change the customer name.

So for example:

I may have:

Customer Name | Location
---------------------------------
Acme Corp | Pittsburgh
Acme Corp | Cleveland
Acme Corp | New York


But Cleveland is no longer owned by Acme Corp, so I'd want to just change it for all time to make it simple JUST for that customer/location combo:

Customer Name | Location
---------------------------------
Acme Corp | Pittsburgh
Jones Corp | Cleveland
Acme Corp | New York

Any advice here? I thought about somehow tagging the columns with a TRUE or a number based on the combo, but I still don't know how to operate across two columns. Thanks for your advice.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Probably a clunky solution, but I guess I could do something like:

1) Setup a temp column
2) Use the formula =if [Customer Name]="Acme Corp" and [Ship To City]="Cleveland" then [Customer Name] ="Jones Corp" else [Customer Name]
3) Do this logic for all the combos I need to change
4) Delete the original column
5) Replace with this column

Maybe a better way?
 
Upvote 0
In Power Query add a new Custom Column. The Custom column formula:

if [Customer Name] = "Acme Corp" and [Location] = "Cleveland" then "Jones Corp" else [Customer Name]
 
Last edited:
Upvote 0
Thanks - that's what I did per my comment above, but was looking for a better way - because of all the changes, the M code is really long and clunky.
 
Upvote 0
2 Tables: CustomerLocations and Corrections.
Merge these tables, add a custom column with the correct name (new name if available else the current name), select the required column and rename the new column.

Code:
let
    Source = Table.NestedJoin(CustomerLocations,{"Customer Name", "Location"},Corrections,{"Customer Name", "Location"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"New Customer"}, {"New Customer"}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "Custom", each if [New Customer] = null then [Customer Name] else [New Customer]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom", "Location"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Custom", "Customer Name"}})
in
    #"Renamed Columns"
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top