External Tools in Power BI Desktop

The July 2020 version of Power BI Desktop released a new feature called the External Tools toolbar.  This was announced back in May during the Microsoft Business Applications Summit, and I have been waiting eagerly for it to be released ever since.  In this short article today I am going to introduce you to this new toolbar, and show you what you can do with it (something to get you started, anyway).

Discoverability is Where it is At

There are actually 2 great things about this new toolbar.

1. The first is that it is now easier to use these external tools than it was before (more on that below).
2. But the second reason that I love it so much is that it creates “discoverability” about the existence of external tools to people that may not have realised such tools exist.

When something is up front and visible, curious people seek to learn more.  When some feature is not visible, then it is impossible to know what you don’t know.  This is the main reason I HATE the fact that Microsoft hides the Power Query application under a non-descript menu item called “get and transform”, and sometimes “transform data”.

Edit: Woah, stop! I just found out that the external toolbar only appears IF you have installed at least one external tool, so I retract what I said – this is the same ol’ Microsoft.  Hide the good stuff so you can’t find it.  Same issue with Power Query and Power Pivot before it.  If you want to see the external tools Toolbar, you must first install a recent version of one of the external tools that was designed to work with Power BI Desktop.

The External Tools toolbar was developed by Daniel Otykier as part of the external developer programme Microsoft has in place (the same Daniel that wrote Tabular Editor).  I think I will ask Daniel to include some “more info” links on this toolbar so that people with curious minds can find out more information about external tools.

Edit: I spoke to Daniel.  Apparently there is a help button already on the help tab!  Well, I didn’t see it – did you?  See my previous rant about discoverability 🙂

There are 3 Main External Tools So Far

There are 3 main external tools that you can use so far as shown below – there are others, I know, that I am not deeply familiar with as yet.

ALM Toolkit was developed by Christian Wade (now works at Microsoft). This tool is used to control deployment of tabular datamodels to SSAS Tabular databases, including Power BI Premium.  I won’t be covering this in my article today.

How Do I get External Tools?

When you first open the External Tools toolbar, there will be nothing to see unless you have first installed one or more of these applications yourself.  Each external tool is a separate piece of software that you have to first install on your PC.  When I first downloaded and installed the July 2020 version of Power BI Desktop and switched to the External Tools toolbar, this is what I could see.

I knew I had both Tabular Editor and DAX Studio installed on my PC, yet I could only see Tabular Editor (as you can see above).  Note that Tabular Editor was greyed out and could not be launched.  So the next thing I immediately did was to update both of those third party software products to the latest versions of the software.  I closed Power BI Desktop before installing the latest versions.

Here are the links for the downloads in case you want to install them yourself (spoiler alert – you should definitely do that).

https://tabulareditor.com/

When you install Tabular Editor, make sure you select “Create Program Menu Shortcut” during the install.  At one time I installed an update without this setting, and I couldn’t find a way to launch the program from the windows menu ?.

https://daxstudio.org/

When I installed DAX Studio, I got this warning

I just clicked the “More Info” link, then “run anyway”.  This software is perfectly safe as long as you download it from the official site.

After launching Power BI Desktop again, I could see both DAX Studio and Tabular Editor – both were greyed out.

Once I opened a workbook with a data model, they both became active and clickable.  I was chatting with some other PBI Pros the other day and they reported that others had a similar experience. Funny thing is that now I don’t get this issue anymore.  The apps are always available and not greyed out.

What Can I do with DAX Studio?

I’m glad you ask.  Quite a lot actually.  Let me give you one thing you can do that I haven’t blogged about before.  One relatively new feature in DAX Studio is the integration of Vertipaq Analyzer (developed by SQLBI.com).

I opened an existing Power BI workbook in Power BI Desktop, and then clicked the DAX Studio launch button on the External Tools toolbar. I just loved how quickly it launched DAX Studio and directly connected DAX Studio to the datamodel running on my PC (running inside Power BI Desktop).  I then switched to the Advanced menu within DAX Studio and clicked on “View Metrics”.  This is the integration of Vertipaq Analyzer.

There is a wealth of information quickly and immediately available, as can be seen below.

Looking at the columns, I can see from my sample data that the largest column of data is the Sales Order Number.

Looking at Column sizes is a great way to mange the size of your model and potentially improve performance (these 2 things are directly related).  For the purpose of this demo, I don’t get any value from the Sales Order Number.  I switched back to Power BI Desktop, went to the sales table (#1), right clicked on the SalesOrderNumber column (#2) and then deleted it (#3).  Then I saved the workbook.

The next step is to refresh the metadata in DAX Studio, then check the stats again.

The column was gone, and the Sales CustomerKey is now the biggest column (not something I can do much about).

In my very simple Adventure Works DB, I saw a file size reduction of 18% from this one action.

There is so much more you can do with DAX Studio and you can find a lot of articles on my blog.

What Can I Do with Tabular Editor?

I discovered Tabular Editor relatively recently and I have written a few articles about that too.  I have one tip below that hopefully will get you going.  But first, I want to point out that when I launched Tabular Editor, the experimental features warning still exists.

There are features in Tabular Editor that are not supported by Microsoft (at the moment).  You can turn this feature on or off in the Tabular Editor preferences.

Now one thing to note.  I turned on the “unsupported” features in Tabular Editor a few months ago. Back then it was not possible to write back to an open datamodel running in Power BI Desktop unless this experimental feature was turned on inside Tabular Editor.  I have now turned the unsupported features off inside Tabular Editor, but I can still save to the datamodel directly from Tabular Editor.  I recommend you leave this turned off (or turn it off now if you previously turned it on).  That way you will stay safe.

Enhanced Metadata

But there is one more thing.  If you want to use Tabular Editor the way I describe in my earlier blogs, and below, you need to turn on the Enhanced Metadata preview feature in Power BI Desktop (it’s in the preview features settings in PBI Desktop).  But please note well – my friends tell me that this preview feature is definitely “preview”, and some people have reported catastrophic failures with this setting turned on.  So turn it on, yes; but make sure you keep incremental backups of your model if you do.  Also read the release notes about this preview feature available from within the preview settings.

Enhanced Format Strings

One thing you can do immediately with Tabular Editor that is currently not fully featured in Power BI Desktop is control formatting strings for values.  To demo the point, note below I have a measure that can return a positive or a negative currency.

I went to the new External Tools toolbar in Power BI Desktop and launched Tabular Editor.  Same as with DAX Studio, Tabular Editor launched immediately with a live, direct connection to the running datamodel on my PC. Using Tabular Editor, I navigated to the Sales table (1), found the measure (2) and then went to the format string in the properties box (3).

There I simply changed the string to something that I needed – much more flexible than Power BI.

\$#,0;(\$#,0);\$#,0

I then saved in Tabular Editor, and the changes were immediately reflected in my report.

Then I thought of my accountant friends saying “we don’t want $$$$$ signs but we do want the brackets for negative numbers”.  So I changed the formatting as follows

#,0;(#,0);#,0

Saved again, and I got this

Wrap Up

These are just 2 very simple things you can do from the External Tools toolbar.  If you want to learn more, explore my blog articles about DAX Studio and Tabular Editor.