Many to one relationship and no relationship found creates "(Blank)"

[Anonymous]

Note: I've seen many posts on the community about painting over this no relationship by excluding or replacing the ID with a defaulted value because they have no data on the data table so no need. My case is the other way around. I have no ID but I have data for the row.

This is along the lines of the table visual:

Club       | Number of Members

-------------------------------------

Bar         |  1200

Golf       |   1800

              |    640

Salsa     |   200

Science |    100

The relationship is on string values since my data is internal and no flags were created. So, keep in mind I am unable to change anything on the original table. (Side note: Can I create custom unique flag values on Power BI data?) The relationships is between two tables:

Data Table:

Club       | Number of Members

-------------------------------------

Bar         |  1200

Golf       |   1800

              |    640

Salsa     |   200

Science |    100

 Lookup Table:

Club       | Member Name | Member Age

-------------------------------------------------

Bar         |  Kyle                   |  NULL

Golf       |   Amanda           |  19

Dance    |   Alfonso            | 21

Salsa      |   Alex                  |  30

Science |    Grant               |  18

Dance    |   Chris                |  26

Dance    |   Patty                |   28

As seen, there is no relationship with Club Dance. I would like to merge the Dance column with the Salsa data. How should go about this? Is there a merge I can do? Perhaps remodeling a calculated column. In the end, I do not want to see any (Blank) values and I would not like to exclude data (as seen here, 640 is a huge piece of the data).

I have already tried creating a new calculated column (but it does not work): Department Revised = IF(ISBLANK([Club]), "Salsa", [Club])

Any suggestions?

Thank you for any help.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try to create a calculated column like this:

Department Revised = IF(table[Club]=""||table[Club]="Dance", "Salsa", [Club])

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

[Watsky]

@Anonymous  Does this work for you?  For your blank value I replaced the blanks with Office Services:

I did the same with Dance to Salsa:

Then I just joined up the two tables and used a Matrix as a visual:

What type of visual are you trying to create with this information? 

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! 👍
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.