Replace Values from Another Table value

[Anonymous]

Hello,

Need your help to write a DAX statement

I have the values in Table A, which needs to be converted to the value in B
The first column ID is a unique column

I have the following question:
- can I achieve this using the Replace function?
- or should I create a custom column and how to do that

Appreciate your inputs

thanks / J

Table A
ID	Value
123	TRUE
124	FALSE
126	FALSE
127	TRUE
128	TRUE
129	FALSE
Table B
ID	Value
126	TRUE
129	TRUE
EXPECTED RESULT
Table A
ID	Value
123	TRUE
124	FALSE
126	TRUE
127	TRUE
128	TRUE
129	TRUE

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

According to my understand, you want to replace the original value from Table A with another from Table B when they have same ID in two tables, right?

You could follow these steps in Power Query:

1.Merge these two tables using "Merge Queries" and expand items.

2.Use Table.ReplaceValue() like this:

3.Remove unnecessary column and rename the new column.

The whole M operation in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCgkKdVWK1QFxTYBcN0efYBjfDI1vjqrcApVriVAdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type logical}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Table B", {"ID"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Value"}, {"Table B.Value"}),
    #"Replace Value" = Table.ReplaceValue(#"Expanded Table B",each [Table B.Value],each if [Table B.Value] =null then [Value] else [Table B.Value],Replacer.ReplaceValue,{"Table B.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replace Value",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Table B.Value", "Value"}})
in
    #"Renamed Columns"

Here is the pbix file.

Best Regards,
Eyelyn Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@Anonymous 

you can try to create a new column

Column = 
VAR a=LOOKUPVALUE(TableB[Value],TableB[ID],TableA[ID])
return if(ISBLANK(a),TableA[Value],a)

[Anonymous]

Thank you @ryan_mayu  and Eyelyn Qin for your quick response. I have accepted Ryan's solution 

@v-eqin-msft : Unfortunately i cannot merge my tables as the table is already a merged table, i just want to replace the value in Table A based on the value from other table B (as the id is key)

The solution from @ryan_mayu  worked well, but the downside is i need to create a new table, for now now, i can manage with this approach 

Thanks/J

[Anonymous]

Thank you Ryan for the prompt reply - your approach seems to be working for creating a new column

Is there a possibility to replace the values in the same Table A (this will save lot of rework needed, as i have already designed the formulas, joined based on the Value Column in A table - Kind regards/J