While some cloud BI tools such as Amazon QuickSight do all their authoring in the browser, Microsoft chose to develop a dedicated client creatively named Power BI Desktop. Its initial release as “Power BI Designer” back in December 2014 was underwhelming and unstable.

But Microsoft stuck to its guns, and by combining Power Query for ETL, the great vertiPaq engine to handle data models in memory, and a decent if uninspired visualization layer, Power BI Desktop gradually grew into a must-use tool for BI developers and analysts working in the Microsoft ecosystem. The relentless stream of monthly updates – even if the pace eventually slowed down somewhat – has been a sight to behold.

Yet anyone doing more than cursory development with Power BI Desktop will grow frustrated by its limitations and inconsistencies, especially if you’re used to tools made for professional developers. Third-party solutions emerged from the user community over the years, and eventually Microsoft wisely chose to embrace them officially.

Introductory Musings on Product Positioning and Historical Developments

This section is meant to put in context the emergence of third-party tools in the Power BI ecosystem. It’s safe to skip if all you want is operational goodies.

Microsoft program managers have stated that they intended to add more authoring tools to the Power BI service eventually, but I believe running the data model on a desktop tool – locally or in a virtual machine – will remain relevant for the foreseeable future, provided you have enough RAM (in my experience, getting to 32GB makes a big difference). It’s really cool to be running Analysis Services on your own PC if you stop to think about what’s under the hood.

Power BI Desktop however suffers from the jack-of-all-trades curse in that it doesn’t excel (bad pun? say it ain’t so!) at anything:

• The ETL user experience is good for “regular end users” thanks to Power Query’s macro-recorder like GUI, but it can be extremely slow and is not very developer friendly. The “Advanced Editor” eventually graduated from obnoxious to lackluster with the addition of color-coding and intellisense, but it remains underwhelming for more demanding use.
• DAX authoring in the formula bar can be aggravating because of silly usability snags.
• Application Lifecycle Management remains a weak point.

Whether you’re looking at the M side or the DAX side, there’s a general lack of a “big picture” perspective of your code base, with things such as global search-and-replace or dependency tracking being obvious gaps. The maturity seen in traditional development tools is nowhere to be seen, which is a bit frustrating coming from a vendor with such a rich history of working with and for developers. I understand Microsoft has business users in mind with this product, but even “regular people” eventually need this type of support.

Instead, in the past couple of years it’s become clear that Microsoft has been more focused on turning Power BI Desktop into the “clicky draggy PowerPoint of data” than making it a very developer-friendly tool. The user community made a concerted effort to fill in the gaps, and with its July 2020 update, Power BI Desktop became officially aware of third-party tools and gave them the ability to write back to the model.

I have mixed feelings about this as I feel it’s a cop out from making improvements that should be native to the core tool, but if Microsoft is not going to focus on developer productivity, I’ll take it. Interestingly, this ribbon was contributed by third-party developer Daniel Otykier. To be fair, that openness to community tools makes Microsoft looks much better than old school enterprise vendors such as Oracle, as Paul Turley posted here. This is now a self-fulfilling prophecy, as Microsoft concluded that enterprise BI development tools are met with community tools, ergo they can spend their resources addressing more mainstream user needs. The folks at SQLbi reached the logical conclusion:

“We suggest moving the development of enterprise-level Tabular models to Tabular Editor, and choosing the version (free open-source or commercial supported) according to the company’s productivity requirements and budget. We also suggest adopting DAX Studio to optimize data models and DAX expressions, as well as participating in crowdfunding to support its development.”

Marco Russo – Development tools for Tabular models in 2021

I’ll also note that Power Query is really a separate product, and in my opinion it would be better off as its own desktop tool that can go back and forth with Excel files, Power BI datasets, Power BI dataflows, PowerApps dataflows, Azure Data Factory wrangling dataflows, Power Query in Power Automate, you name it… but I digress.

OK Olivier, Nice History Notes, But Why Should I Bother Again?

If you don’t feel like you’re outgrowing Power BI Desktop, you’re probably using Power BI at a fairly small scale, which is entirely fine, but the Power BI platform has grown far beyond basic self-service BI. And even at a small scale, the effects of time alone make the need for auditing and documentation tooling ever clearer.

While you might wonder what the fuss is about if you’re just getting started with a new, small model from scratch, the need for more powerful tooling becomes more apparent as you:

• Add more models
• Use more advanced functionality, from row level security to aggregations to calculation groups
• Run into performance issues
• Have more than one developer
• Need to maintain all this as your Power BI use becomes more and more complex over time: more data, more measures, more users, more workspaces, higher expectations, personnel rotation, etc. etc.

Trust me on this, Power BI Desktop alone is not enough to do sustained serious work on the Power BI platform.

Enough Already, I’m Convinced! Getting Started with 3rd-Party Tools in the Power BI Desktop Ribbon

First off, external tools will only show up in the ribbon if specific JSON files are present on your PC, which requires you to use versions of these tools not older than July 2020. So start by installing the latest DAX Studio et. al. if you haven’t already done so.

Another significant dependency is that you need to enable the enhanced dataset metadata preview, otherwise the icons will be displayed as disabled. I ran into issues with this feature when I tested it, so like some other consultants I follow, I stayed away from it for fear of wreaking production models. Edit: since I initially wrote this entry, enhanced dataset metadata made it to General Availability, I haven’t run into problems since. This is a good example of why you want to stay close to the Power BI Desktop release cycle. It’s OK to be behind by a month or two, but if your IT locked you with a version from a year ago then you’ll be missing out on structurally important changes.

Without further ado, here’s an overview of what these tools are about, focusing on ETL and data modeling for now. You can download and install them separately or use the “Business Ops Installer” to get them in one swoop, but I’m told the latter is not necessarily up-to-date. In March 2021 support of external tools reached General Availability.

1. DAX Studio: Diving Deep Into your Data Models, with a Side Dish of VertiPaq Analyzer

If you’re going to use a single third-party tool to complement Power BI Desktop, DAX Studio is it. It lets you see under the hood of your data model in ways that Power BI Desktop can’t. There’s a learning curve to it, mainly because there’s a steep learning curve to DAX. Don’t just EVALUATE it, adopt it (bad pun intended).

Here’s how to get started with it, including how to connect to the ever-changing IP port if you’re not going to use the new integration with Power BI Desktop. There’s a lot going on here and explaining DAX Studio in detail is beyond the scope of this entry.

Years ago SQLBI created another tool to analyze storage structures in PBI and Analysis Services models called VertiPaq Analyzer. It’s very useful in case you’ve added many tables to your model and are now faced with a big PBIX file, which can lead to performance issues or even push you beyond what’s allowed in shared capacity (capping datasets at 1GB each) or even dedicated capacities.

The metrics returned by VertiPaq Analyzer are now available in DAX Studio, and can be output to small .vpax files that contain just metadata and stats, but not actual data, and can be opened in Excel to see further details. This will help you diagnose that, say, using GUIDs takes a lot of room in your model that could be saved by using surrogate keys instead.

Finally, DAX Studio also gives you the ability to export the underlying data to Excel without facing the volume restrictions imposed by Power BI Desktop and the service.

In 2022 SQL BI launched Bravo, a much simpler tool to find which tables and columns take the most space in your model, reformat your DAX, create a Date table, and export data.

2. Tabular Editor: Unlocking Calculation Groups & Working at Scale

Tabular Editor is alongside with DAX Studio, the other heavy hitter in the Power BI tooling world. Two versions are available: the free open-source 2.0, and the commercial version 3. TE3 adds DAX editing, debugging, querying, and scripting; a C# macro recorder; and generally speaking, more polish and productivity than can be found in the free version. A detailed feature comparison can be found here.

2.1. Cut Down Redundant Measures and Control Formatting with Calculation Groups

Tabular Editor was originally built with Analysis Services in mind. It has however become very relevant to Power BI which now supports calculation groups – including in a shared capacity aka Power BI Pro – but lacks a user interface to create them.

A DAX limitation that’s been frustrating me to no end is the fact that FORMAT turns numbers into strings, making it useless to control how chart axes, labels etc. look. Calculation groups however let you apply a format string expression to measures without that side effect. This unlocks much more flexible visualizations, as explained by Business Intelligist in the next video.

For more use cases, read 10 ways you can use Calculation Groups in Power BI and watch the video below, there’s really a lot going on here.

2.2. Other Tubular Efficiency Gains with Tabular Editor

There’s much more to the tool than just support for calculation groups, with many other features that can accelerate large scale work. Its measure dependency tree for one is a good example of functionality that I feel should be native to Power BI Desktop. Because you’re working against model metadata rather than the model itself, changes are instantaneous where Power BI Desktop can feel sluggish whenever you update expensive DAV measures with big dependency chains.

Where DAX Studio is the tool of choice within a model, Tabular Editor is the strongest tool across models. It will cut through a lot of repetitive work in case you’re working on a many different datasets over time.

Scripting and custom actions in particular deserve to be explored as an entirely new way to approach DAX development beyond the confines of Power BI Desktop. See how Matt Allington uses it to edit/replace code inside all DAX measures, among other useful script snippets. See all its advanced features here, and this Introduction to Tabular Editor by Alex Powers.

Reid Havens for instance posted a very useful two-line script to expose DAX code to end users via the measure description field:

3. Power BI Sidetools

This tool reveals a very good understanding of productivity bottlenecks inherent to Power BI development. It’s a bit of a Swiss Army knife, with:

• DAX debugger that autogenerates debug tables as you write measures and their variables
• Export tool to refreshable Excel files
• Saving DAX and M codes to files
• VS Code integration

4. Visual Studio Code: M and TOM Advanced Work; Notepad++; Visual Studio

4.1. Syntax Highlighting for M and DAX

We just mentioned Visual Studio Code as a complement to Power BI Side Tools. The first and most straightforward use for this free and nimble editor is to install its Power Query / M extension and use it as an ETL editor. It’s not going to be recognized by Power BI Desktop as a substitute for its advanced editor, so you’ll have to copy and paste. At first I thought I had to save a query to a .pq file for the M code to be recognized as such, but kind souls on Twitter pointed me to the “Select Language Mode” menu (shortcut: Ctrl+K M) in the bottom right corner:

There’s a similar extension for DAX.

4.2. PowerShell Scripting Black Arts

But wait! Undeterred by the lack of ribbon integration, the fearless Ben Grubaudo came up with a solution using PowerShell to write back from VS Code to Power BI and Excel files. Bananas, as they say in Power BI lingo! You’ll need to install PowerShell 7 if you don’t have it already (here’s how to check).

If you want to execute PS scripts from within VS Code, you’ll want to select it as the shell you’re executing in VS Code (otherwise you might be running commands against PowerShell 5, which won’t work). If you’ve never done development before, well, welcome to dependency hell (wait till you try npm or Python)! In the case of Edit-Datamashup, it needs to be executed from outside of VS Code with VS Code closed, but other scripts such as Search-DataMashup can be launched from within VS Code.

Incidentally, Microsoft just released Pylance, a language server for Python, to provide autocomplete, error-checking, jump-to-definition, code formatting, and other goodies while writing and reviewing code. When you realize how sophisticated is the tooling of more mature general-purpose languages, the DAX authoring experience in Power BI Desktop then looks downright primitive. Microsoft’s design choice, centered on an Excel-like formula bar, is both a blessing to ease entry for a mass audience, and a curse when you undoubtedly eventually need to work beyond its confines.

4.3. Tabular Object Model: Advanced Sorcery

VS Code can also be connected to a Power BI data model via the Tabular Object Model (TOM), as described by Phil Seamark here. While you probably won’t want to go through that rather involved process for day-to-day work, this is a promising approach for bulk work such as copying measures between reports or migrating from Azure Analysis Services to Power BI Premium.

Watch Phil’s blog for follow-ups on this, starting with Automatically create measures in Power BI using VS Code (C# code sample).

4.4. Notepad++: The Original Option for Syntax Highlighting M Code

Before this existed in any Microsoft tool, Matt Masson and Lars Schreiber added support for M as a user-defined language for the beloved Notepad++. From a code-formatting perspective, it’s functionally equivalent to the VS Code option above, so it’s more about which code editor you prefer to use.

Note however that Lars stopped supporting the tool as Power BI and Excel improved their PQ editors, so you might be missing out on newer M functions. I still thought I’d mention it if only for the historical record or in case you can’t/won’t run VS Code.

4.5. Visual Studio: for SSAS Die-Hards and Custom Connector Developers

The full-fledged Visual Studio is a very distant grandfather to the open source VS Code. I’ve used it a couple times to work against Power BI – namely to migrate a Power BI model to SSAS – but like Paul Turley details in this post, it’s a bit of a bloated product so good luck with that. I’m mentioning it here for completeness, in practice it sees little use in the Power BI community.

For a completely separate use case there’s the Power Query SDK that’s designed to help with the development of custom connectors and is overkill for “regular” M ETL.

5. ALM Toolkit: A substitute for Git That Knows PBIXes Inside Out

A PBIX file is really a zip file with XML and JSON files in it. To the best of my knowledge this has been reverse engineered by the community but is not officially documented for the public, and “hacking” these internals, while useful in a number of scenarios, is not supported. A drawback of how this file format is structured is that it’s just a binary file to traditional source control tools such as Git.

To alleviate that, Microsoft’s Christian Wade and MAQ Software developed the ALM toolkit (guys, get an SSL certificate for your website!). ALM Toolkit also lets you make metadata-only model updates, unlike Power BI Desktop which overwrites the dataset. This is especially important to avoid erasing historical partitions created for incremental refresh. Ideally you’ll use this tool in combination with deployment pipelines.

The Target in the Power BI service follows this syntax, where WorkspaceName and DatasetName are the assets names, not their IDs from the URL.

Dataset: powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName;DatasetName

6. Power BI Cleaner; Power BI Comparer; Power BI Model Documenter

The always helpful Imke Feldmann created two pointed tools well worth mentioning here, even though they’re separate PBIT/XLXM templates rather than external tools you can open from the toolbar. Read the instructions carefully as they won’t work unless you disable privacy restrictions that get in the way of running native database queries.

• Power BI Cleaner lets you identify unused measures, calculated columns, relationships, and pretty much everything else in a model except incremental load policies. If you’ve grown a large model over months or years, it’s very likely you have some cruft in there.
• Power BI Comparer is an Excel template that counts and tracks down changes between two PBIT files. “Git blame” for Power BI if you will.

Marc Lelijveld created a somewhat similar tool with Model Documenter.

7. Power BI Helper to Fix That Mess!

This tool from Radacad feels a bit less single-minded in its focus than everything I’ve listed so far, but if you inherit messy, undocumented Power BI entities it may prove useful in getting a handle over what’s going on. Features run the gamut from exporting and beautifying M script to auditing columns, tables, and relationships, to publishing and copying across workspaces. It is now “ribbon ready”.

8. Analyze in Excel

I’ve always been frustrated by Analyze in Excel’s reliance on jumping through several hoops and using the old .odc office data connectors as opposed to loading Power BI datasets from Get Data in Excel. SQL BL strikes yet again by bringing Excel much closer to Power BI Desktop with the AnalyzeInExcel external tool. This has become less useful now that you can Get Data from a Power BI dataset in Excel.

9. Beyond M and DAX: R and Python with Power BI

R and later Python were the original third-party tools recognized in some capacity by Power BI, namely to generate visuals by sideloading the respective engines and IDEs. You can also use R or Python in Power Query, but that’s done via the native Power BI UI, not in third-party editors. These capabilities have existed for years, and their core purpose is to bring the world of R/Python into Power BI.

The new external tool functionality opens up opportunities for additional ways to use these languages directly on top of Power BI Desktop. David Eldersveld lists some scenarios in this entry:

• Code model documentation.
• Export as much data as your local RAM will allow (as an alternative to using DAX Studio for that purpose)
• Visualize the Power BI data in a fully interactive way with Python libraries such as Altair or Bokeh by loading the data into a panda dataframe. R/Python visuals in Power BI are turned into static images so they can be the recipient of cross filtering but not the emitter.
• Blend Power BI data with other sources in Python.
• Run DAX queries from a Python script or Jupyter notebook, a bit like count.co in spirit. I can see how I might use this for tests, training, and documentation.

Some of these ideas are very intriguing as they might help expose and “unlock” Power BI data models beyond the confines of the Power BI service. I’ll have to think more about how deployment would work, but this could be big for embedded scenarios without having to shell out big bucks for Power BI Embedded.

10. More External Tools and How to Build Your Own

Read the official documentation that explains the underlying architecture and limitations of external tools, then see these entries for examples of what can be built and how to do so:

• Kasper de Jonge’s: Creating a Time intelligence “External Tools” app for Power BI Desktop
• Erik Svensen
  • External tool to connect Excel to the current PBIX file
  • Same with Tableau Desktop
  • Power BI Report Builder
• Phil Seamark: Connect to an AS model
• Soheil Bakhshi: Registering SQL Server Profiler as an External Tool in Power BI Desktop
• Steve Campbell: Hot Swap Report Connections
• Marcus Wegener: Export2Dataflow

11. Monkey Tools for (Excel) Data Models

Technically Monkey Tools is an Excel add-in, but some of its more advanced features are also valuable to Power BI modelers. Namely, QuerySleuth gives you a dependency/precedent tree view of your queries, while ModelSleuth helps document data models.

Unlike the aforementioned tools, this one is a paid product.

12. BI-Related Tools Beyond This Entry’s Scope

As I said there’s a broader BI ecosystem with many other tools helping with the various facets in BI projects:

• Data access, e.g. Azure Storage Explorer, Azure Data Studio, or SQL Server Management Studio
• Administration and governance, e.g. BI Sentinel, Power Update
• Write-back and planning: Inforiver, Power Planner, Acterys (available as an External Tool)
• Custom visuals such as Charticulator, Power BI Report Builder (which will be added to the external tool ribbon)

13. Further Related Musings

• Making Sense of Power BI Programmatic Options
• Spreadsheets Get Smarter About Structured Data, At A Steep Price
• Power BI: The Missing Productivity Features
• Ideas to Improve Software Documentation, Raise Productivity of Mere Mortals