Results 1 to 3 of 3

Thread: Find Replace and Update

  1. #1
    Neophyte CrazyMexican's Avatar
    Join Date
    Dec 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Find Replace and Update



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Everyone,

    New user here, I am trying to do the following between two tables that have several thousand lines.

    I have two Tables, Table1 and Table2. Table1 has 3 columns "Names" "Value" and "Updated", Table2 has two columns "Names" and "Values". All of the names in Table2 can be found in Table1 but not the other way around. I need to be able to do the following two things: Replace the value in Table1 with the value on Table2 that corresponds to the same name, and change the "Updated" column to 1 if there is a match even if the value remain unchanged (see Travis and Anastasia in my example image).

    I have been trying to do this using a modified version of the m code provided here https://blog.crossjoin.co.uk/2014/06...n-power-query/, but I am open to other ideas.

    Thank you.


    Click image for larger version. 

Name:	Example.JPG 
Views:	22 
Size:	47.0 KB 
ID:	9482

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Code:
    let
        Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Merged Queries" = Table.NestedJoin(Source, {"Name"}, Table2, {"Name"}, "Table2", JoinKind.LeftOuter),
        #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value"}, {"Table2.Value"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if [Table2.Value] <> null then [Table2.Value] else[Value]),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Table2.Value] <> null then 1 else [Updated]),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Value", "Updated", "Table2.Value"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Value"}, {"Custom.1", "Updated"}})
    in
        #"Renamed Columns"

  3. #3
    Neophyte CrazyMexican's Avatar
    Join Date
    Dec 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016
    Bob, this is amazing, it is exactly what I needed, I am so used to traditional programming that I was complicating this more than I had to, it goes to show that I have plenty to learn. Thank you for the help.

Tags for this Thread

Posting Permissions

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