The External Tools ribbon is a feature that has been available in Power BI Desktop since the July 2020 release. This feature should be considered essential to anyone who is regularly working in the Power BI space. In this article I recommend my top five External Tools, that I consider essential to any Power BI developer.
What is the External Tools Ribbon and where do I find it?
The External Tools ribbon provides easy access to powerful tools that are installed locally on your computer and registered with your Power BI Desktop. This feature has been overlooked by some organisations because it is a community driven development feature, but for anyone regularly working in data model development within Power BI Desktop, this should not be considered optional, but an essential feature required to develop Power BI reports efficiently and effectively.
Unfortunately, just like the glory days of the Sony PlayStation’s Net Yaroze, the “secret” black PlayStation that allowed in-the-know hobby enthusiasts opportunity to develop their own PlayStation games, Microsoft has decided to hide the External Tools ribbon from view from you unless you are in-the-know. In order to see the External Tools ribbon on your Power BI Desktop, you will need to have installed at least one External Tool.
Don’t sweat. By the time you finish this article, you will be in-the-know, you will have the External Tools ribbon and some of the most valuable external tools available to help you develop reports quickly and efficiently.
Nota Bene:
Before installing external tools, keep the following in mind:
- External Tools are not supported in Power BI Desktop for Power BI Report Server.
- External Tools are provided by external, third-party contributors. Microsoft does not provide support or documentation for external tools.
- External Tools ribbon is enabled by default, but can be disabled by your IT department, so may not be available to all who read this article.
Installing your first External Tool
Installing External Tools is easy. Just click on the link of the tool you wish to install below. Download the associated file. Install the program as you would any other program.
That’s it. Open Power BI Desktop and find your new External Tools tab!
Now, let’s find out which External Tool you want to install.
1. Say Hello to Bravo
Bravo for Power BI is the first essential tool on my list. It has bumped Tabular Editor to 2nd place simply because it is valuable to every developer from a freshly trained rookie to rock star developers.
Bravo is a free tool created by SQLBI that helps you modify and improve a Power BI model with a simple user interface. There are four main features that Bravo offers to help you develop your reports:
• Analyse Model: Discover the more expensive columns and tables in your data model
• Format DAX: Format one or more measures to best practice DAX with just two clicks.
• Manage Dates: Create date tables and common time intelligence measures using predefined or customized templates.
• Export Data: Export the data of Power BI tables to Excel and CSV files
As a non-programmer, I believe the ability to format every measure in your workbook with just two clicks is reason enough to justify installing Bravo on your PC.
2. Edit en masse with Tabular Editor
Tabular Editor is a great tool for helping you to make changes quickly and efficiently in a Power BI data model and is the tool I use the most aside from Power BI Desktop.
Tabular Editor provides an intuitive hierarchical view of every object in your Power BI data model (including hidden calendar tables). Columns, Measures and Hierarchies are arranged in Display Folders by default. You can edit properties of one or more objects, by (multi)selecting them in the tree. A DAX Editor pane with syntax highlighting lets you easily edit the expressions of Measures, Calculated Columns and Calculated Tables. Other features of Tabular Editor include:
While Tabular Editor can be used by seasoned developers to achieve amazing things in Power BI with scripts and calculation groups, it is the ability to create or modify multiple measures and apply the changes all at once, potentially saving significant “Working on it” time, that is the reason Tabular Editor is ranked number 2 in my list. Tabular Editor is perfect for developers of large reports across all skill levels.
If you are keen to add Tabular Editor to your Power BI Desktop power tools, there are 2 versions of the External Tool you will need to choose between.
– Tabular Editor 2.x (free) – GitHub project page
– Tabular Editor 3.x (commercial) – Home page – this version includes DAX IntelliSenseTM
We have written a number of Tabular Editor articles to help with becoming a master of this tool:
3. Clean your workbook up with a click
With Measure Killer for Power BI in your toolkit you will be able to remove the clutter, keeping your PBIX workbooks lean and performant. This tool is best suited for intermediate developers that work collaboratively on reports, or who are responsible for maintaining reports they have inherited from other developers.
Measure Killer ranks number 3 in my top 5 because it allows you to easily identify unused measures in your data model and delete them with a script in Tabular Editor. It also offers an opportunity to trim your tables by removing unused columns with a simple m-code script that can be pasted into the Advanced Editor of the Power Query Editor. Matt has reviewed Measure Killer previously, so I will steer you to that for more information on this power tool.
4. Performance Tune your Data Analysis eXpressions
DAX Studio is a fabulous free tool that allows you to directly query your Power BI data models. There are four H.E.L.P. reasons for adding DAX Studio to Power BI Desktop:
- Help you write more complex DAX formulas that contain “tables” as part of the formula
- Extract a list of measures from your data model into a spreadsheet
- Learn how to write DAX as a Query Language
- Performance test and tune your measures
It is not immediately obvious (particularly to beginners) exactly how using DAX Studio can add value, so to help with this, the blog linked below is a simple reference on getting started with DAX Studio.
DAX Studio makes its way into slot 4 because it provides insight into the performance of your DAX Measures, allowing you to refine, test and enhance your formulas which in turn improves the overall performance of your Power BI reports. DAX Studio is not a tool for the newly initiated developer, but if you are comfortable with your DAX and ready to understand what happens under the hood, then you are ready to start Demystifying DAX with DAX Studio.
5. Analyze your local Desktop model in Excel
After you have published your data model into the Power BI Service, the Analyze in Excel feature allows you to connect Excel to published Power BI datasets, so that you can view and interact with them using PivotTables, charts, slicers, and other Excel features. Analyze in Excel for Power BI Desktop is an External Tool for Power BI Desktop that offers these features to an unpublished Power BI Data model.
With a single click, Analyze in Excel for Desktop creates a new Excel PivotTable connected to the data in the open Power BI Desktop report, allowing you to navigate the Power BI model using the Excel you know and love. You can save the Excel thin report and share it, knowing your data is safely locked away in Power BI Desktop.
Analyze in Excel for Desktop is my number 5 power tool.
6. Browser Launching Honourable M ention
Although the M Formatter External Tool didn’t quite make it into my top 5 essential External Tools for Power BI Desktop, it certainly came close. Before Bravo for Power BI, I used a DAX Formatter browser launching external tool, and together with the M Formatter browser launcher the pair would have made the top 5 list as a bundle.
This Power BI Desktop External Tool opens your default web browser to the online Power Query M Formatter. While the Power Query M Formatter website isn’t as essential to your report development as the ability to correctly format your DAX, if you like to dive into the Advanced Editor window of Power Query, it is nice to discover a polished M script waiting for you.
Nota Bene:
The M Formatter External Tool (developed by Brian Julius) cannot be installed as such. To add the M Formatter external tool to Power BI Desktop, you will need administrator rights to your PC. Download the tool from the link above and copy the .json file into the following folder location
C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools
When you reopen Power BI Desktop, you should see the M Formatter in your External Tools.
External Tools a Plenty
In this article I have provided my top 5 External Tools for Power BI Desktop, but there are so many other incredible External Tools available in the community, I encourage you to explore them all.
Let me know in the Comments below if you have a favourite that wasn’t mentioned here.
Be the first to comment