Top 7 Pain Points for Power BI users working in the real world

This article is brought to you by JBI Training, the UK's leading technology training provider.   Learn more about JBI's Power BI training courses including Power BI - Visualisation, Power BI - Dax Data, and Power BI - Beyond the Basics

1) many to many:

Problem: tables are connected by a many-to-many relationship, this is accepted by PowerBI but can lead to problems, incorrect calculations and bad performance.

Solution: in the query editor create a table that would have a unique list of combinations from the two original tables and then use a 1-to-many relationship toward the two original tables. This table is called a "bridge table"

2) source spreadsheet moved

Problem: when opening Query Editor you get an error message saying that the source file cannot be found; or your refresh does not work anymore because the source is not found

Solution: Open "Data Source Settings", from Desktop or from Query Editor. Click on "Change Source" and browse to find the new location of the file.

3) empty (null) data

Problem: your data has many null values; null values can create issues when reporting

Solution: this problem can have many different causes and therefore different solutions. 

If you have done a merge, check that you have used the right field in the merge; 

if you have just imported the data and nulls are not in the original, it might be a problem with data type, if possible solve the issue in the source

as a last resource, if you cannot solve it in other ways, just right click on the column and select replace nulls

4) incorrect dates

Problem: you cannot create time intelligent calculations because your dates are not correct

Solution: in order to use time intelligence in Power BI you need to have a separate date table with all the possible dates from the first date of your records to the last (or possibly some years more); you can create this in Query Editor starting with a formula like this : Duration.Days(Duration.From(EndDate - StartDate)) or in DAX, using CALENDARAUTO()

5) duplicates

Problem: your data has many duplicates of the same row

Solution: if you have done a merge, check that the merge is using the correct columns; 

if the duplicates are coming from the load, right click on the column (in query editor) and select "remove duplicates", just be aware the whole row will be removed, not just the value in the column

6) bad data modelling

Problem: your report is not performing well, several calculations are incorrect ...

Solution: this might be caused by a bad design of the data modelling, the data model that works best in Power Bi is a star model as the Vertipaq engine has been created on that bases

7) force PowerBI to work like Excel

Problem: you have a very complex excel spreadsheet and you want to reproduce EXACTLY the same spreadsheet in Power BI

Solution: Power BI is not excel, it works differently and it has different strengths. In order to tackle this issue the best way is going back to the source and try to reproduce the final result understanding the logic and reproducing the logic, rather then the formulas.

Alternatively you can keep your calculations in Excel and use PowerPivot, which has the same engine as Power BI, but it uses the Excel front end

Collated by JBI's instructors based on course delegate feedback from the following courses:

Power BI training course

Power BI Beyond the basics training course