Replacing blanks with strings in related tables?

[powerbiuser101]

Hi,

I have a cosmetic problem and I'm trying to replace blank values, with a string, when 2 tables are in a simple Many-to-1 relationship. I have 2 tables, "Table1" and "location".

Table1:

Ticket       DC

11	ABC
22	ABC
33	DEF
44	GHI
55	GHI
88	JKL
66	DEF
77	JKL
99	JKL
8	XYZ

Location Table:

Prefix       Region

ABC	CA
DEF	WA
GHI	TX
JKL	CA

The problem is that when I use any visual, Table1[Ticket] = 8 will be blank for Location[Region], because there is no match (expected). But I need the Location[Region] to be "unknown" in this scenario.

I can do this simple enough if I create a calculated column with an IF condition in Table1, but it's mandatory to use Location[Region] as it's connected to many other tables. Any tips on how I can achieve this?

[GeorgeBuster]

Hi Powerbiuser101,

You can edit your Location Table in the Query Editor of Power BI and "add" the row you need.

Best,

Jorge.

[powerbiuser101]

Hi @GeorgeBuster and thanks for your reply.

Unfortunately for the scenario I face, there's actually 10K+ rows for "Table1" and 35+ rows for "Location" and ~20% of Table1[DC] values are completely mislabeled. 

So there would always be a mismatch, which is a different sort of problem in itself. 

[GeorgeBuster · Accepted solution]

Hi powerbiuser101,

Well, in Power BI if there is a mismatch in a relationship, you can't change the blank behaviour. You need to have the rows with the value "Unknown" and their relationship.

It doesn't matter if you have to add 1 or thousands of rows, in the query editor you can easily achieve it. You can 'full' join the tables and replace the null values (the rows that haven't correspondence) by "Unknown". Later some steps of cleaning.

Hope you can achieve it,
Jorge.