Find Replace and Update

[CrazyMexican]

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/0...replacements-of-words-in-text-in-power-query/, but I am open to other ideas.

Thank you.

 

[Bob Phillips]

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"

 

[CrazyMexican]

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.