Optimize Power BI Data Models with Tabular Editor
Tabular Editor is an external tool that can help you see what's going on in your Power BI data models. It sits more on the tech user end of the spectrum, but has some handy best practice analyzer and other functionality that can help beginners and seasoned pros alike.
If you want to optimize your Power BI data models, this tool could help you out.
In order to follow along with this post, make sure you are using the latest version of Power BI desktop. External tools functionality was added to Power BI desktop in the July 2020 release.
Step 1: Download Tabular Editor
Tabular Editor is a free tool developed by SQLBI and is available for download from their website. In order to use it with Power BI desktop, you must first download the external tool:
Once Tabular Editor is downloaded, you will be able to use it from within Power BI desktop.
Step 2: Open Power BI desktop
If you have Power BI desktop open already - close it. You will need to restart after installing Tabular Editor.
Open the Power BI desktop file you wish to optimize.
Step 3: Open Power BI data model in Tabular Editor
Now that you have Tabular Editor installed with the latest version of Power BI desktop, you'll see a new tab in the ribbon for 'External Tools'.
Click the Tabular Editor button in the External Tools tab in the ribbon.
Tabular Editor will open in a new window and you will see a Model tree on the left hand side:
Step 4: Run Best Practice Analyzer
Step 4A: Download the JSON rules file from GitHub
Go to GitHub at this URL:
https://github.com/TabularEditor/BestPracticeRules/blob/master/BPARules-PowerBI.json
Click the green "Code" button to download .zip file
Save the BPARules-PowerBI.json to your PC.
Step 4B: Run Best Practice Analyzer
From Tabular Model, click the Tools tab and select Best Practice Analyzer
Click the icon to "Manage Rules"
Click "Create new Rule File"
Click "OK"
Select the BPARules-Power BI.json file you downloaded.
Select your new Rule collection.
Click "OK"
Step 5: Fix Data Model based on BPA
Some rules cannot be fixed without custom code or manual updates in Power BI desktop. We won't focus on those in this post and will leave that for the pros to figure out.
However, some fixes can be automatically applied.
Select a rule from the Best Practice Analyzer.
If the wand icon is not greyed out, this rule can be automatically fixed.
Note: You should only apply the magic wand fix if you're sure you know what that fix will do. It's a good idea to save your Power BI desktop file with a different name before starting this process in case you need to revert back to a previous version.
If you want to preview the script first, click the scroll icon to the left of the wand. If you know the fix will work, click the wand and the fix will automatically be applied.
Save the Tabular Editor file.
Return to your Power BI desktop file to see the changes instantly applied.
Save the Power BI desktop file.
