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

Thanks Thanks:  0
Likes Likes:  0
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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 05:59 PM.

  4. #4
    Board Regular
    Join Date
    Jun 2009
    Location
    Pittsburgh
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,798
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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