Creating a new Text column in Table A and updating the value from a Text column in Table B.

Hi afaber ,

Hi, you can achieve this by creating a calculated column in Table A that retrieves the corresponding value from Table B when a matching key exists and defaults to Table A’s original value when no match is found. With a proper relationship in place where Table A is on the many side and Table B is on the one side, the logic checks for a related value in Table B and uses it if available, otherwise it falls back to Table A. If a relationship is not available, the same result can be achieved using a lookup based on the common key..

 I have attached a sample .pbix file demonstrating both approaches and the expected output for reference.

Thank you.

In Power Query, navigate to Home=>Combine=>Merge Queries=>as new

In the dialog (which also shows my sample Tables A&B

In the resulting table:

Add Column=>General=>Custom Column

-try [TableB][Column2]{0} otherwise [Column2]

Then merely delete the TableB column:

Assuming you have TableA and TableB in Power Query, the code for the Merge would be:

let
    Source = Table.NestedJoin(TableA, {"Column1"}, TableB, {"Column1"}, "TableB", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "New Column", each try [TableB][Column2]{0} otherwise [Column2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"TableB"})
in
    #"Removed Columns"