In response to customer feedback, we introduced support for external tools within the Power BI Desktop authoring experience last year, and thanks to the great feedback and help from our community of Power BI users and partners previewing this feature and integrating their tools, we are now able to announce General Availability (GA).
As you might already know, external tools integration enables you to leverage a wide range of additional semantic modeling features, DAX query/expression optimization and authoring utilities, and application lifecycle management (ALM) capabilities in Power BI Desktop to boost your productivity as a world-class Power BI solution creator. Besides the all-time classics, ALM Toolkit, Tabular Editor and DAX Studio, which shouldn’t be missing in any BI Pro’s toolbox (see the previous public preview announcement), new modelling tools and integration ideas are quickly emerging. The following screenshot shows a few of them on the External Tools ribbon.
Conceptually, external tools integration makes it very easy for external tools to connect to the data model inside a Power BI Desktop (PBIX) file. You only need to create a reasonably straightforward *.pbitool.json text file and drop it into the %CommonProgramFiles(x86)%\Microsoft Shared\Power BI Desktop\External Tools folder of your Power BI Desktop client machine.
As celebrated in the screenshot above, tools authors were quick to take advantage of this exciting extensibility option, such as David Eldersveld integrating Python to access the model data, Erik Svensen using Excel to test Power BI solutions avoiding the overhead of uploading the PBIX file to the Power BI service first, and then Mike Carlo and Erik taking it to the next level with Power BI Report Builder integration so that pixel-perfect paginated reports can be authored without having to deploy datasets to the Power BI service, and Soheil Bakhshi integrating SQL Server Profiler to trace the AS engine communication inside Power BI Desktop and fully exporting the data, table names, column names, and relationships all at once in CSV format packed in a zip file with his Power BI Exporter tool. Phil Seamark has created a Visual Studio Code-based DAX Formatter tool to help you automatically format your DAX expressions by using the DAX Formatter API provided by SQLBI. Steve Campbell is bravely switching live connections to direct connections to an open Power BI report with the innovative Hot Swap Connections tool. Another useful tool worth mentioning is Marc Lelijveld’s Model Documentation tool, which lets you analyze and document a Power BI data model in a separate Power BI report. Using Power BI to analyze Power BI—Brilliant!!
Not every tools author can be mentioned, unfortunately. If the community keeps building tools at this rate, we might soon run out of real estate on the External Tools toolbar. Maybe we need to add collapsible ribbon groups. See for yourself! If you download Mike Carlo’s Product Business Ops tool, you can quickly add a large selection of useful tools to a Power BI Desktop near you, as you can see in the following screenshot. If you are interested in learning more, checkout the Story and the Why of Business Ops.
Of course, external tools integration not only helps you use the tools of others, but it also empowers you to build your own specialized modelling tools and extend in this way the core functionality of Power BI Desktop to take your productivity to the next level.
With this in mind, we are announcing the following additional enhancements along with GA so that you can leverage even more capabilities. See the full list of supported operations at https://aka.ms/ExternalTools.
- Support for dynamic format strings
- Metadata translation
- Object-level security (OLS).
By using external tools, such as Daniel Otykier’s Tabular Editor, you can easily add calculation groups to a Power BI dataset, now with full support for dynamic format strings so that you can override the original format string expression of a measure and have the correct format consistently applied in any Power BI visual (not just table, matrix, and card). This was also a requested improvement on Power BI Ideas.
Regarding metadata translations, note that Power BI supports datasets with multiple locales as long as the dataset is hosted on Power BI Premium, Premium per User, or a Power BI Embedded SKU. Datasets on shared capacity cannot take advantage of metadata translations yet. So, as long as your dataset is on a supported Premium or Embedded SKU, you can now add multiple languages to your datasets easier than ever, thanks to external tools integration. For example, Metadata Translator can translate the captions, descriptions, and display folder names of your tables, columns, measures, and hierarchies in any of the Power BI-supported languages in a matter of seconds by using the machine translation technology of Azure Cognitive Services. Metadata Translator also lets you export the strings into .csv files for convenient bulk editing in Excel or a localization tool and then import the finished .csv files to apply the changes. The following screenshot shows this external tool in action, adding more than 25 languages to a single Power BI dataset.
Overall, Metadata Translator supports more than 50 languages across more than 330 locales. At least theoretically, there is no limit regarding the number of locales you can add to a Power BI dataset so you can easily support even the broadest international audience with your reports on Power BI Premium. For reference, see Supported languages and countries/regions for Power BI in the product documentation. See also the animation below, which illustrates the multi-language report experience in English and Portuguese. Metadata translations work in Power BI as well as in non-Power BI clients like Excel.
As mentioned earlier, external tools integration GA also comes with read/write support to add object-level security (OLS) to a Power BI dataset. Power BI Desktop enables you to define row-level security (RLS) to restrict data access at the row level based on filters defined within the roles. Yet, users can still see all tables and columns in the data model. There is no user interface for OLS in Power BI Desktop, but you can easily switch to external tools, such as Tabular Editor, and define OLS in this way, as in the following screenshot. By using OLS, you can fully hide sensitive tables and columns from users where necessary. For more information about OLS, see Object-level security (OLS) in the product documentation.
And that’s it for this important external tools integration milestone. Now that this integration option is GA, we hope it is going to help you take your productivity in production to the next level. We are very grateful to our amazing community of Power BI users and partners helping us preview this feature and integrating existing and new tools into Power BI Desktop. Special thanks also go to Daniel Otykier who created the External Tools ribbon as part of the Power BI Contributor Program. We are amazed at the quality of Daniel’s work and his unwavering determination to empower the Power BI community to achieve more. If you see Daniel, please thank him yourself! Thank you all! Let’s celebrate by keeping on innovating to everyone’s benefit!