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:
- 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 
  
IDValue
123TRUE
124FALSE
126FALSE
127TRUE
128TRUE
129FALSE
  
  
Table B 
  
IDValue
126TRUE
129TRUE
  
EXPECTED RESULT
  
Table A 
IDValue
123TRUE
124FALSE
126TRUE
127TRUE
128TRUE
129TRUE
  
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)

5 Replies

  • 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.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

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

    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