Implementing error checks in Power Query

All too often, I've seen this scenario play out which really slows down Business Intelligence teams…

• A Power BI solution gets built and delivered to the business.

• Some time passes, and the Power BI developer moves on to other things. In doing so, they lose familiarity with the logic of the report.

• The business users notice that the report is not calculating values as expected, and notifies the BI team.

• A developer (either the original one or a different developer) digs in to the report, and takes a long time to diagnose and correct the issue.

While the business is pleased when the issue is resolved, the issue impacts the overall trustworthiness of the reports provided to decision makers. This can become a real roadblock to creating and maintaining a data-driven culture within the organisation.

And these issues can accumulate if Power BI developers don't follow good development practices. Over time, the BI team may become trapped in firefighting mode, with most of their time spent on fixing reports that keep breaking, instead of building new solutions to support the business.

How do you stop this from happening?

Like with many things, prevention is better than cure.

In my experience, following the DataOps Principles as closely as possible is an effective means for delivering effective and robust analytics solutions.

For the scenario above, the following two DataOps Principles are particularly relevant:

• Quality is paramount

  “Analytic pipelines should be built with a foundation capable of automated detection of abnormalities and security issues in code, configuration, and data, and should provide continuous feedback to operators for error avoidance.”

• Monitor quality and performance

  “Our goal is to have performance, security and quality measures that are monitored continuously to detect unexpected variation and generate operational statistics.”

Essentially, these principles speaks to the need to minimise the technical debt caused by ‘waiting for something to break, and dealing with it only when someone in the business complains’. I see this attitude taken all too often, especially when the team feels they are too busy to follow these principles. Ironically enough, it is usually the case that they are so busy becuase they’re cleaning up messes that could have been avoided by following these ideas in the first place!

John Kerski has some amazing articles about implementing DataOps in Power BI. I would highly recommend checking it out if you commonly deal with the scenarios described above in your organisation.

Ok great. But seriously, how do you stop this from happening?

Alright, I’ll get off my soapbox and get to some practical strategies :)

Solving with simple error checks

A simple way I've found to ensure that quality is always maintained is to use error checks throughout my Power Query logic.

I've worked with a lot of solutions requiring csv extracts rather than databases recently ¹ , and there can often be instances where the source system isn't capable of enforcing data integrity regarding business rules. So adding these error checks has helped me ensure that, if any business rules are violated, the report does not even refresh—stale data is better than incorrect data. ²

As an example, let's consider a set of employee work patterns, which indicate how many hours each employee works on each weekday...

Work patterns are usually recorded cyclically, and in different organisations they may repeat on a weekly, fortnightly or four-weekly basis. We’ll assume a simple weekly work pattern in this example.

In the source system that tracks these patterns, the staff FTEs get entered manually based off of a 40-hour work week ³ . The table above shows these manually entered figures, where the FTE's for Alice and Snehal correctly match the hours worked throughout the week. But for Lindsay and David, their work patterns do not match their weekly hours⁠—Lindsay works 8 hours every day, so his FTE should be 1.0 not 0.8. And David only works 4 days a week, so he should have 0.8 FTE not 1.0.

If we want to enforce our business rule that the FTE must be consistent with the weekly work hours, we can intentionally raise an error when this happens, so that any scheduled refreshes will fail and the incorrect data isn’t brought into our data model.

Power Query has a nifty function called Error.Record that helps us accomplish this. Rick de Groot has a very comprehensive documentation article on all the ways you can use the function, as described here.

To set up our error checking workflow, we can bring the work pattern data into Power Query and add a nested calculation that does the following:

• For each row, sum up the work hours for the week, and convert it into an FTE

• Filter down to only rows where this FTE check doesn't match the manually-entered FTE

• Count how many times this data entry error happens

• Return a descriptive error message if any data entry errors were detected

• If no error were detected, return the original table

Here's the error that gets returned when we apply this logic in Power Query….

And here's the full M code for this scenario…

let
    #"First Step" = #"Sample Work Patterns",
    #"FTE Error Check" =
    let
        #"Add FTE check column" = Table.AddColumn(#"First Step", "FTE Check", each Value.Divide(List.Sum({[Monday Hours], [Tuesday Hours], [Wednesday Hours], [Thursday Hours], [Friday Hours]}),40), Int64.Type),
        #"Filter to error rows only" = Table.SelectRows(#"Add FTE check column", each [FTE for Work Pattern] <> [FTE Check]),
        #"Error Count" = Table.RowCount(#"Filter to error rows only")
    in
        if #"Error Count" > 0
        then
            error
            Error.Record(
                "Calculation error",
                "One or more FTE values were entered incorrectly. Please request for HR to investigate. "
                )
        else #"First Step"
in
    #"FTE Error Check"

If these errors get introduced to a published dataset rather than one in Power BI Desktop, you'll get an error message displayed in the Power BI Service that instructs you on how to resolve it.

This can help you to diagnose and potentially correct the issue without even having to open up the report in Power BI Desktop.

Extending to be more descriptive

If there is only one mismatching FTE value, it's helpful to also return the Employee ID for the offending record in the error message, to speed up troubleshooting. And when there is more than one mismatch, you can return a count of the mismatches, and a list of the offending Employee IDs.

Here's how you can extend the logic, and the messages returned for both of the above scenarios… 

let
    #"First Step" = #"Sample Work Patterns",
    #"FTE Extended Error Check" =
    let
        #"Add FTE check column" = Table.AddColumn(#"First Step", "FTE Check", each Value.Divide(List.Sum({[Monday Hours], [Tuesday Hours], [Wednesday Hours], [Thursday Hours], [Friday Hours]}),40), Int64.Type),
        #"Filter to error rows only" = Table.SelectRows(#"Add FTE check column", each [FTE for Work Pattern] <> [FTE Check]),
        #"Error Count" = Table.RowCount(#"Filter to error rows only"),
        #"List of Employee IDs with mismatches" = Text.Combine(Table.TransformColumnTypes(#"Filter to error rows only",{{"Employee ID", type text}})[Employee ID], ", ")
    in
        if #"Error Count" = 1
        then
            error
            Error.Record(
                "Calculation error",
                "The work pattern for Employee ID = "
                & #"List of Employee IDs with mismatches"
                & " has an incorrectly entered FTE. Please request for HR to investigate. "
            )
        else if #"Error Count" > 1
        then
            error
            Error.Record(
                "Calculation error",
                Text.From(#"Error Count")
                & " work patterns have incorrectly entered FTEs, with these issues occurring for Employee IDs {"
                & #"List of Employee IDs with mismatches"
                & "}. Please request for HR to investigate. "
            )
        else #"First Step"
in
    #"FTE Extended Error Check"

You could also fork the offending rows off into a separate query for inspection. Reza Rad has a good post on this exception reporting approach if you want to go even deeper into this topic.

Try it out for yourself!

Download the Power BI report file I used in this post to play around and build your own understanding hands-on.

---

1. My preference is always for a database connection, but third party SaaS providers rarely like to expose their databases to customers directly. ↩

2. Ideally you would also set up monitoring for refresh failures to proactively deal with these issues. That is beyond the scope of this article. ↩

3. Note that in practice, it would be appropriate to ignore the manually-entered FTE column and calculate the FTE off of the work hours. But bear with me, as this example is just to illustrate the idea of implementing error checks.  ↩

---