Having the correct data types in your Power BI model is essential for a fast performing dashboard. For the most part, Power BI does this automatically for you in the Power Query editor when connecting to a data source.
Power Query accomplishes this by scanning the first hundred or so rows to determine the most appropriate data type for each field of your table. As awesome as this is, sometimes it makes a mistake. The most common example I've encountered is that my currency fields are marked as text. This can throw your models way off, especially for aggregates.
Many users may close out of the Power Query editor right after connecting to their data source, and they won't realize the error until the model is being constructed. Once found, the fastest way to correct this is to use the Data Type feature in the Modeling section of the ribbon, as opposed to opening the query editor.
However, changing the data type here does not change the M script in the Power Query editor.
So what's the difference? Well, it seems like setting the data type in the report design ribbon allows the model to refresh faster. I set up a spreadsheet with 6 columns, each with all rows filled in with a single integer, 1. I added one additional text column just to provide some filtering capabilities.
I then connected to the the worksheet with Power Query. I created 3 queries.
1. Connects to the data source and changes the data type.
2. Connects to the data source but does NOT change the data type. I changed the data type for this query using the report design UI (DAX).
3. Connects to the data source but does NOT change the data type. I'm using this one as the Control of sorts.
After loading the model into Power BI and setting the data types accordingly. I timed the refresh...
We can see in the screenshot that the Control is fastest, which is to be expected, though not by much. The surprising part is, by setting the data types within the Power Query editor with M, it's almost twice as slow than if you do it within the report design UI ribbon.
The refresh might be faster... but what about the actual data model and visuals? Maybe the charts and graphs update/react faster with the Power Query M method (since it looks like the data types are applied before they hit the Power BI model)? Well, I created several graphs repeatedly layered across the report, each using the separate methods.
Perhaps I need a bigger data set to see the difference in speed, but I see none. Both methods seem to have the same speed when updating and reacting to filter changes.
The only down side I can see to applying the data types via the report design UI ribbon, is that the types will be removed if the sources' column names change at all, which can be an hard to catch and reapply. Though, I still have to recommend setting the data types within the Power Query editor, because it's a sure way your model is using all the correct data types from the start. It may take longer to refresh, but it looks to me like it's more robust.
Building an Access Database? Check out my ebook on Amazon!
