Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Replace a value with data from a different table if blank

Hello! I have a requirement to replace any blank values in a column with a different value.  While this can be done with an IF statement in the same table, the replacement value is in a different ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Figured it out!  Added this to a new column based on the test data: 

    Replace =
    IF('Table1'[Table 1 "names"] = "",
        (
            VAR Test1 = LOOKUPVALUE(Table2[Location 1 keys], Table2[Nameskey], Table1[Names keys])
            VAR Test2 = LOOKUPVALUE(Table3[Location 2 keys], Table3[Location 1 keys], Test1)
            VAR Test3 = LOOKUPVALUE(Table4[Table 4 "account"], Table4[Location 2 keys], Test2)
            Return Test3
        ),
        Table1[Table 1 "names"]
    )
v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, Anonymous ;

Since the data is sensitive, you can create some fake data, and the results you want to output.


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello v-yalanwu-msft ,

     

    I created a sample report with sample data.  I couldn't attach the report directly, but found an upload service suggested on this forum.  

    Below is a screenshot from the report.  The end goal is to replace that blank value in the 'Table 1"names"' column with "AccountValue2" from the 'Table 4'account' column.  All of the relationships are the same as the real data.  However, there are several other tables in the live data connected to these tables, which is why I can't modify the relationships (the data is imported from Dynamics 365).  


     



    Upload files for free - Table Value Replacement Fake Data.zip - ufile.io