Download the excel file and follow along!
Following on from my last article on how Power Query can be used in Finance departments to increase efficiency, we created a report and automated the lookup for product category. By creating a Power Query report we will save time and effort every time we create this report. However, there was one issue, this report wasn’t 100% correct.
Error Checking
There’s nothing worse than getting a call with a colleague saying “I don’t think this report is right” or “Are you sure this is correct?”. This is why error checking is key. There are simple ways to add in simple checks into your Excel reports to try and prevent the inevitable.
Building in Error Checking Into Your Reports
In all reports I recommend adding in a settings tab. This tab can be used for creating constants and error checking.
Above, I’m adding in checking to the report tab, to ensure the balances back to our original data. No matter how you prepare your report, with vlookups / sumifs or Power Query. Checking your end result is as expected will save that embarrassing phone call.
Power Query Report Next Steps:
If you’re following along with the series and you’ve also added the error checking you’ll notice our report doesn’t balance! It was intentional (I promise), and a nice segue into error checking.
Let’s Fix Our Report
When we merged our data with our product table, lookups work slightly differently in Power Query. If we did a formula Excel would have returned the first value. In Power Query, it will add additional rows for everything it finds a match for – certainly a good point to remember.
If you go to the product tab, we have mapped our products twice! To fix our report and to clear our variance, all we have to do is clear out the duplicate rows in the table.
Remove the highlighted rows. Head to the data menu and click “Refresh All”. Now, check the report and the Settings tab and the variance should have rectified itself.
One fixed report that will save you time every time you need to update it. With built in error checking to boot!
Leave a Reply