Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
Hi Powerbiuser101,
You can edit your Location Table in the Query Editor of Power BI and "add" the row you need.
Best,
Jorge.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |