10 May 2022
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
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"
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.
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
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()
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
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
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: