June 14, 2020
Preparing Work Environment in Power BI Desktop
This article covers best practices for setting Power BI Desktop version 2.76.5678.782 (January 2020).
Power BI consists of Power BI Desktop and PowerBI.com. Every Power Bi project starts in Power BI Desktop. Properly setting up our work environment is crucial. In this article we’ll cover all the important settings in Power BI Desktop. Settings are managed in File > Options and settings > Options. This opens a new window. Settings are ordered in two groups: Global and Current file. Some Global settings are repeated in Current file, since they can also be applied for current file only. We’ll mark there settings throughout the article.
Settings come in two groups: Global and Current file.
Data Load (Global)
Auto date/time for new files
We turn this setting off. If left turned on, Power BI will create hidden calendar table for every date and time filed in our data. For example: we have a table with three date columns called Date, OrderDate and PaymentDate. Power BI will create three separate hidden calendar tables, which will slow down our model and user experience.
Data Load (Current file)
We also turn off the setting here. We don’t want any redundant calendar tables that would slow down our model.
Autodetect new relationships after data is loaded
We turn this setting off. Automatically generated relationships are often incorrect. We want full control over building relationships in our data model.
Power Query Editor
Display the formula bar
We turn this setting on. This way, we can see, enter and edit formulas. Every transformation in Power Query Editor is stored as a step in the Applied Steps window. Steps are written in M language. We can see and edit the M formulas in the formula bar. We could also edit the formula in Advanced Editor by clicking View > Advanced Editor.
Enable M Intellisense
We turn this setting on. This will provide us with helpful suggestions while writing formulas or table names.
We always set this to Always ignore Privacy Level Settings. Default setting Combine data according to file’s Privacy Level Settings could cause a problem. If we have data in files with different privacy settings, we can end up with queries that don’t see each other, hence we can’t merge or append them.
“” Microsoft recommends using the default setting Combine data according to file’s Privacy Level Settings, but this can cause difficulties in practice.
Here we can set the language or region that Power BI uses for reading data. Selected language will affect the date and the decimal separator. In case of working with data stored in European format, we have to be cautious to set the correct language or region even before starting the project.
Power BI comes in two versions, that differ in the way the update. Power BI application, which we find on the homepage of Power BI, will update automatically. We can’t turn the automatic updates off in this version, we can only choose not to be notified about the updates. Updates are usually released on a monthly basis and are occasionally followed by an update fix. New updates are often not production ready and can cause problems in active Power BI projects. This is why we usually don’t want automatic updates. We can avoid them by using the second version of Power BI: instead of choosing the app on the Power BI homepage, we opt for installation file. We can recognize it by the filename extension .exe. We decide when we want to update in this version of the tool, which gives us greater control.
Here we find latest functionalities, added to Power BI. We usually want to use all the added functionalities, except for regionally specific ones. Options in this section will depend on version of Power BI Desktop and will change with time.
Preview features setting: we select all the functionalities, except for regionally specific. This list of options will change with time, as new functionalities are added. (Functionalities in the image above are from January 2020, version 2.76.5678.782).
Auto recovery (Global)
We keep the default setting on, so our work is saved and can be restored in the case of technical issues or power outage. Default setting is 10 minutes but can be changed to any other value.
Auto recovery (Current file)
Disable auto recovery for this file
We turn this on when we’re working with large files. Auto recovery would slow down the performance.
Report settings (Current file)
Power BI allows users to extract data from reports and save it to a file. For example, they can export data from a chart to a table and save it locally. This is why we disable the option to export data from reports when we’re working with sensitive data.
Saving Settings as a Template
Good practice is to create a template and use it for every new Power BI project. This way we save time and make sure our model works optimally, without redundant functionalities that would slow down our workflow or user experience.