Often times our Power BI reports contain information that is inexplicably incorrect. A value is missing from a table. A metric is way out of the ball-park. There are duplicate columns or bars. We have a weird 01/01/1970 date. An issue is uncovered when validating our insights. This article provides a simple, step-by-step work flow for identifying and resolving such issues.
Checking the Source Data
First step when encountering such issues in Power BI is to check the source data the report is using. Open Power Query by right-click on the query that contains the incorrect data (in the panel on the far right), click ‘edit query’, move up to the first step (in the panel on the far-right) and filter to the data that’s causing an issue:
Is it coming through correctly? If not, then we can either:
- Resolve it in the source system (correct the data in SharePoint, for instance, then refresh the report). This is the most resilient method, ensuring our report mirrors and maintains its integrity with its source.
- Transform the data – by finding and replacing text, for instance (the less resilient and more questionable, but often easiest and sometimes only solution)
Checking the Power Query Steps
If it is coming through correctly, but not showing up in the report, click through the remaining steps; is it still showing correctly? If not, then perhaps we’ve applied a filter to remove it, we’ve removed a column containing the information, or a merge with another query has obfuscated it. Correct the steps to resolve the issue.
Checking the Report Settings
If it is still correct in Power Query and not appearing in the report then:
- There may be a relationship mismatch – for instance if we build a relationship between two tables based on an email address column, and one table has some Camel.Case emails and the other lower.case ones then the ‘relationship’ will be a divorce. [email protected] does not marry with [email protected]. Solve this by changing the source data or transforming it – lower case the Camel.Case email addresses for instance
- Check is any filters are being applied to your report page or specific visualisations on it; does clearing them fix it?
- Is an interaction active that is hiding expected data from view? Click ‘View/Format/Edit interactions’ to resolve, or click outside of a selection on a visualisation to remove the filters.
- Is our report using a bookmark that is out of date? Open the bookmarks pane, update any, and republish
Fixing Data Issues in Power BI: Cheat Sheet
Here’s a handy cheat sheet for the workflow above; do get in touch in the comments if you can see any improvements we could make to it:
