Forum Discussion
Creating a new Text column in Table A and updating the value from a Text column in Table B.
Hi
I have Table A and Table B, they have a common relationship.
I need to create a new column in Table A and populate it with the value from a similar column in Table B where there's a match, if there is no match the new column in Table to be populated with the value from Table A's column.
To explain in simpler terms:
BEFORE
Table A:
Column 1 [Key relationship to Table B] = 123456
Column 2 = Open
New Column :
Table B:
Column 1 [Key relationship to Table A] = 123456
Column 2 = Closed
AFTER if match found
Table A:
Column 1 = 123456
Column 2 = Open
NewColumn = Closed
If no match found, then NewColumn = Table A. Column 2's value.
Hope this makes sense 🙂
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"
9 Replies
- cengizhanarslan
Super User
Since you already have a relationship between Table A and Table B, you can use RELATED().
NewColumn = COALESCE ( RELATED ( 'Table B'[Column 2] ), 'Table A'[Column 2] )Hi. The syntax seems to be accepted, however, when running it in Transform, Add Column I get this error:
Expression.Error: The name 'COALESCE' wasn't recognized. Make sure it's spelled correctly.
I did check the spelling, so not sure what the issue is.
- cengizhanarslan
It's DAX not M, so the answer has to be implemented using Calculated Columns.
- MasonMA
In Power Query, you can merge Table A with Table B by Left Outer join, expand Table B column and bring in the relevant column e.g., Column 2 from Table B (rename it to Column2_B), then create a 'custom column' with fallback logic:
if [Column2_B] <> null then [Column2_B] else [Column 2] Would you be able to share the syntax for the merge command?
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.Hi afaber ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
ronrsnfldIn 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"
