Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Location
    Pittsburgh
    Posts
    143

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


    Use Power Query to change one column based on criteria in two columns.
    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.

  2. #2
    Board Regular
    Join Date
    Jun 2009
    Location
    Pittsburgh
    Posts
    143

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

    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?

  3. #3
    New Member
    Join Date
    Mar 2015
    Posts
    26

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

    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 by SOQLee; May 16th, 2017 at 04:59 PM.

  4. #4
    Board Regular
    Join Date
    Jun 2009
    Location
    Pittsburgh
    Posts
    143

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

    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.

  5. #5
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,730

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

    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"
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com