Forum Discussion

adoster's avatar
adoster
Resolver I
4 years ago
Solved

Replace Values from one table to another

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:

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

 

 

Excel_TableB:

IDCode
1A
2B
3C
4D
5E

 

Desired Result:

SQL_TableA

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

 

 

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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