Power Query
1. Import or DirectQuery: Before your start developing the Tabular model, decide which data Connectivity mode suits your requirement. Read through the Microsoft documentation to understand the differences.
Tip: DirectQuery mode can be changed into Import mode. However, the vice versa is not possible.
2. Disable auto date/time option: In Power BI Desktop, you select File > Options and settings > Options, and then select either the Global or Current File page. On either page, the option exists in the Time intelligence section.
When the option is enabled, Power BI Desktop creates a hidden auto date/time table for each date column. Disabling this will reduce the model size. Only enable this option if required.
3. Tick the “Always Allow” Parameters option through Power Query editor. After this you start using the parameters on the connections.
4. Parameterize the Dataset connections to switch between Dev, Test and Prod easily.
5. Power Query Table modification steps that are helpful.
- Rename the table (give a meaningful name)
- Remove the unnecessary columns
- Rename the columns (give a meaningful name)
- Verify and change the data type of the columns where required
6. Handling DateTime columns for performance.
- Remove the columns if they are not required
- Change the datatype to just date when the time is no longer required
- Split the date and time from the datetime columns if time is required
7. After you bring in the data, categorize the Queries.
8. Query folding: Query folding is the ability for a Power Query to generate a single query statement to retrieve and transform source data. With Query folding, Power BI refresh times will be faster. “View native Query” is enabled when the Query folding is enabled on the table.
9. Use Incremental Refresh with large tables when you are using Premium workspaces. Main benefit with this is, it has a massive impact on Model refresh times. For more information, read the documentation on how to implement Incremental refresh.
10. Other Power Query Modelling tips.
- Integer key columns (surrogate keys) are preferred as Primary key columns. Handle this at source. This will have a massive performance improvement when you are dealing with Facts which have millions of records.
- Above applies to the Date dimension too, use a an integer key column (20231231) as a Primary key column instead of a Date column (31/12/2023).
- Ensure Fact tables refer to the surrogate keys of the Dimensions instead of business keys.
- If the source is a database engine, modify or transform the source tables in the DB, rather than working with complex calculations and transformations using Power Query editor. For example, using Semantic Views with computed columns instead of tables.
Power BI (Report, Data & Model Views)
11. Ensure your Date dimension is marked as a Date table from the Report View.
12. Do not use implicit measures. Verify and update the Summarization property on the integer columns. Easiest way to do this is through the Model view, from here you can select multiple columns and update the Summarization property.
13. From Model View, update each of the General & Advanced properties of the Tables, Columns, Calculated columns, Measures, Calculation groups, Calculated tables etc.
14. Add Calculation Groups rather than duplicating measures for Time Intelligence calculations. Calculation groups in Analysis Services tabular models | Microsoft Docs.
15. From Model View, join the Dimensions and Fact tables. This is where, you have to join the key columns between tables. Relationships on Integer Key columns has a better performance.
16. Relationship handling between different tables.
- Setting the Cross-filter direction to “Single” instead of “Both”
- Reduce the no. of inactive relationships
- Refrain from using Many to many (*.*) Cardinality. If required, introduce a bridge table and change the relations to Many to One (*.1) and One to Many (1.*)
Helpful tips for Advanced Tabular Model Developers
- Become familiar with “Performance analyzer” pane in Power BI desktop to optimize your Measures and Models.
- Get familiar and start using the below External tool add-ins to FastTrack your Tabular model development.
- Familiarize about Power BI REST APIs to administrate, manage and modify various components in the Power BI Service.
- Triggering the Power BI refreshes from other applications. Such as ADF, Logic Apps, Power Automate.
Simple SQL BI blogs 