Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Replace Values from Another Table value

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: -...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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.