Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
dzzxyzz
New Member

Error if no match on join

Hi, I am having trouble setting up a power query such that the power query will fail if there are any rows in my first table that do not match with any rows in a second lookup table, for a left join.

 

To provide more context, I have a 'main table' that has Cost Center as a column, and I want to join to a lookup table for Cost Centers to pull in columns related to the Cost Center. The main table points to an Excel file that automatically gets refreshed (i.e. the file gets replaced) on a regular basis, so that my dashboard can also refresh easily. But, if there are any new Cost Centers that show up in the main table's Excel file that are not in the lookup table, I want the dashboard to throw an error so that I can manually investigate / add the required rows to the lookup table (as opposed to excluding those rows by using an inner join, for example).

 

BONUS: On a similar vein, I was wondering if there's a way to also throw an error if a table has a particular column with duplicate values (e.g. if the Cost Center lookup table had duplicate rows for Cost Center).

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

Problem 1 - If there are any new Cost Centers that show up in the main table's Excel file that are not in the lookup table, I want the dashboard to throw an error

When the correspoding values don't exist in Lookup table, all values from Lookup table will be null. 

Then you can check for the existence of the null in any field of your choice (choose a field which can't be null) and mark error in the custom column.

Note, you can also check just before expanding your result by Table.IsEmpty as well.

if Table.IsEmpty([Column] then "Fail" else "Pass"

 

2. if there's a way to also throw an error if a table has a particular column with duplicate values

You will need to group the table and take count and do a self merge to take count in a separate column. 

Create a custom column to say if count > 1, then duplicate.

Hence, in a custom column you can say

= if [Column]=null then "Fail" else "Pass"

Edit - Another approach is to use following

if List.Count(List.PositionOf(Source[Letters],[Letters],Occurrence.All))>1 then "Duplicate" else "Not Duplicate"

 

All these techniques I have demonstrated in following file - https://1drv.ms/x/s!Akd5y6ruJhvhuSOtm_KabLBuzL7F?e=34HFkh 

Hi Vijay, how do I "mark errors" in the custom column? Tried to google but couldn't find the syntax for specifying errors.

 

Here are my applied steps in the query currently:

dzzxyzz_0-1647352761859.png

 

And here is my code in the Advanced Editor:

dzzxyzz_1-1647352911898.png

 

I also don't understand your "other approach" - where exactly would I be putting this line of code with the if statement?

Vera_33
Resident Rockstar
Resident Rockstar

Hi @dzzxyzz 

 

When you do a left outer join, add a custom column to count the rows of the lookup table, if it is 0 then no row in lookup table, if it is greater than 1 then duplicated rows in lookup table

Table.RowCount([lookupTable])

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors