Replace Values from one table to another

[adoster]

Hi!

Created a simple version of what I am trying to do.

I have 2 tables. Relationship on column ID.

Want to replace "Code" values in main SQL_TableA with values from imported Excel_TableB when the Code value in Table A = "Error"

SQL_Table A:

ID	Code
1	Error
2	Error
3	Error
4	Error
5	Error
6	X
7	Y
8	Z

Excel_TableB:

ID	Code
1	A
2	B
3	C
4	D
5	E

Desired Result:

SQL_TableA

ID	Code
1	A
2	B
3	C
4	D
5	E
6	X
7	Y
8	Z

I tried the following in Power Query

= Table.ReplaceValue(
#"Changed Type",
#"SQL_TableA"[Code],
#"Excel_TableB"[Code],
Replacer.ReplaceText,{"Error"}
)

Result = Expression.Error: A cyclic reference was encoutnered during evaluation

[Anonymous · Accepted solution]

Hi @adoster ,

In Power Query:

let
    Source = Table.NestedJoin(#"SQL_Table A", {"ID"}, Excel_TableB, {"ID"}, "Excel_TableB", JoinKind.FullOuter),
    #"Expanded Excel_TableB" = Table.ExpandTableColumn(Source, "Excel_TableB", {"ID", "Code"}, {"Excel_TableB.ID", "Excel_TableB.Code"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Excel_TableB", "Custom", each if [Code] = "Error" then [Excel_TableB.Code] else [Code]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Code", "Excel_TableB.ID", "Excel_TableB.Code"})
in
    #"Removed Columns"

Use DAX:

Column =
IF (
    'SQL_Table A'[Code] = "Error",
    LOOKUPVALUE ( Excel_TableB[Code], Excel_TableB[ID], 'SQL_Table A'[ID] ),
    'SQL_Table A'[Code]
)

Best Regards,

Jay 

[mh2587]

Append both the tables with coulmn ID