In a previous article I have described how to use Fuzzy merge to match misspelled data to valid categories. With the sample data at hand, all values could be matched to existing categories. But what can you do if there are some entries that cannot be matched to existing categories?
I would create an “unmatched” value to the categories table before loading to the data model and assign the “unmatched”-value to all unmatched entries. Then I would create a measure that counts the entries within the unmatched category. I would then create a data driven alert, based on this measure. That would trigger an e-mail to the person responsible for maintaining the list. Or, if responsibilities are a bit more complex, trigger a flow in Power Automate that could handle certain conditions to be followed as well.
Let’s look at some details:
Add an “unmatched” value to the categories table for exception reporting
First I select the category table and deselect “Enable load”, as I want to use this table only for the match. The table that shall be loaded to the model will contain the value for the unmatched items as well. Therefor I reference this query and create a new query. There I add a new row with the following formula:
Categories & #table({"Column1"}, {{"Unmatched"}})
Exception reporting for fuzzy matching
Replace unmatched values
In the new table with unmatched values, I check the “Categories” column and replace nulls by “Unmatched”:
Replace null by “Unmatched”
Create measure for data driven alert
I want to trigger off a data driven alert if there are unmatched items in the category column. So I’m going to create a measure that counts them like so:
MissingCategories =CALCULATE( COUNTROWS(DataWithMissingMatches), DataWithMissingMatches[Category] = "Unmatched")
Place it as in a card visual on a dedicated “Exception reporting” page to be easily used in the service to create the data driven alert . Follow the steps in that link to set up the data alert in the Power BI service.
Create a flow for more complex alerts
If you don’t want to simply send emails to one or a group of people, you can also trigger a flow in Power Automate instead. Therefore, click on the small print at the bottom of the dialogue.
Trigger a flow from a data event in Power BI
This will take you to another website that starts the triggered flow. You will be a prompted to confirm or change your credentials and then forwarded to page with a Power BI trigger. In there you’ll be prompted to choose the alert Id and all your existing alerts will be shown. … Except the one you’ve just created. That’s because you haven’t saved the alert in the service yet, but have instead jumped straight to the flow.
The alert ID will only show up after the trigger is saved in Power BI service
So it’s best to select the small print and be forwarded to the flow page, but once there, return to Power BI service and save and close the alert. Then go back to the flow and continue the process and you will be able to choose the trigger and move on.
Enjoy and stay queryious 😉
What is your scenario here? Usually, the problem with fuzzy matching is wrong matches (not unmatched), unless you are going for an exact match.
Hi Frank,
use case is everywhere where data is entered manually that should match a collection of existing entries and where no technical solution is implemented to enforce only allowed values for being entered.
Imagine Excel sheets where people fill in customer names for example. Misspellings should be matched by the Fuzzy match, but when they enter new customers names in there that are not yet included in the CRM or ERP-system (or wherever that matching list comes from), then blanks might be returned.
Having an exception reporting could trigger new customers being created in the source-system.
/Imke
Should the responsible person be responsible end to end for any data mismatched?
This could be a tool to help him speed up the work outside of the dataset, but shouldn’t replace for him.
Why adding an “unmatched” value to the categories table must be done on another reference table otherwise a cyclic reference error will show up?