Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity

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 has 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 to be installed on your PC. So start by installing the latest DAX Studio et. al. if you haven’t already done so.

Computer, enhance!
Computer, Enhance!

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.

Watch this if you don’t see your external tools or want to add new ones, otherwise you’ll get Adam’s annoyed frown stuck on your face and you don’t want that

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 a long 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.

Marco Russo seen playing the world’s smallest violin as he’s mercilessly picking on your dire lack of DAX skills

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.

With a proper Italian diet, you too can be thin like Marco.

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.

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

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.

Heh, I know you guys are hopeless at DAX but let me try yet again. So once upon a time Filter Context and Row Context were in a boat…

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.

Yes!!!

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.

Think of calculation groups as DAX measure transformations

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 might be strongest across models. Perhaps its most limiting factor is the lack of IntelliSense while authoring DAX, though it does know when a measure is incorrect. This will be addressed with the forthcoming Tabular Editor 3. On the other hand it can format measures to make them more readable, as per DAX Formatter best practices.

It’s not DAX if it’s not formatted.

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.

Tell your friends you’re a Tabular Editor advanced user, I’m sure they’ll be impressed

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

Do your users keep asking what’s the formula behind a DAX measure?

3. Power BI Sidetools

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

The insides of your PBIX files, revealed

4. Visual Studio Code: M and TOM Advanced Work

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:

Heard in the Microsoft corridors: “let me show you my PowerPoint presentation of PowerShell code extracting PowerQuery code to ease Power BI development”

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).

Launching VS Code via PowerShell Core 7 to load all the M code from a PBIX file in one page. This is loaded in a temporary file, change it then save and close it if you want the edits saved back into the PBIX source. Do this on a backup file!

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.

Running a PowerShell 7 terminal 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.

The formula bar works as a one-off editor, but doesn’t scale when you have dozens of inter-dependent measures

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).

Don’t let TOM and .bim intimidate you, it’s just metadata. This is accessible from Tabular Editor too.

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.

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.

6. ALM Toolkit: A substitute for Git

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!).

Christian appears to be smiling, but he’s crying inside because guys in a cube don’t have ALM Toolkit installed on their laptops.

7. 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.

8. Power BI Cleaner and Power BI Comparer

The always helpful Imke Feldmann created two pointed tools well worth mentioning here:

  • 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.

9. 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”.

You’ll get hooked on tracking down dependencies…

10. 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.
Python BI

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.

11. 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:


Rory Neary walking through model documentation
Nice and easy

12. 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.

This is the end of a long post and we ran out of pithy captions.

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

As I said I left aside for now the broader BI ecosystem, which I may expand upon in a future entry to include:

  • 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: Power Planner, Acterys (available as an External Tool)
  • Visualization, e.g. Charticulator, Power BI Report Builder (which will be added to the external tool ribbon)
  • Custom visual development (that’s a completely different type of project and skillset, closer to web development than to BI)

14. Further Related Musings

10 thoughts on “Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity

    1. otravers Post author

      Thanks Vitali for pointing these out, I was aware of both and knew I was forgetting stuff while writing the entry, which I updated accordingly.

      Reply
  1. Lars Schreiber

    Hi Oliver,

    very nice list of very useful tools. Thanks. Regarding my Notepad++ Editor for M I have to confess, that I stopped supporting it, since Power BI Desktop (and now also Excel) has these features by itself.

    Cheers,
    Lars

    Reply
  2. Jørgen Wulff Rasmussen

    Hi Olivier

    Very Impressive compilation, and a big help to get an overview for the developers

    As it seems like a lot of my fellow countrymen does not search for new knowledge, if it is not introduced to them in their own language, I hope that you will allow me to do a translation with minor modifications that reflects my view of the Power BI world as an intro.

    regards
    Jørgen

    Reply
    1. otravers Post author

      Hi Jørgen, thank you, I’m glad you found it useful. Feel free to translate it in Danish as long as you credit me and link back to my entry, and separate your introduction from my content so that your readers can easily tell apart your opinion from mine. I appreciate that you reached out.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *