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 ;

RELATED is generally used in one-to-one or one-to-many relationships, so your example is not suitable for using the RELED function, maybe you can use the LOOKUPVALUE() function,
Since I can't restore your model relationships, I don't know much about the specific connection relationship fields of your 4 tables; So can't give a precise solution, can you share a simple small example of four tables? And what are the columns of the four table join relationships?


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 !

    Thank you for the help!  To answer your question, here are how the tables are linked together.  Because the data is sensitive I can't give specific examples, so if you need more info than this just let me know and I'll try to clarify. 

     

    The data I'm trying to share are both text based.  Table 1 contains names, table 4 contains locations.  

     

    Table 1 is related to table 2 through a unique name ID (IE: 12345 for John).  These show up multiple times in each table.  

     

    Talbe 2 is related to table 3 through a unique location ID.  This shows up multiple times in table 2

     

    Table 3 is related to table 4 through a unique account ID.  This shows up multiple times in table 3.