Contenidos
The July 2020 release of Power BI Desktop released a new feature called the External Tools toolbar. This was announced in May during the Microsoft Business Applications Summit, and since then I have been eagerly awaiting its release. In this short article today, I'll introduce you to this new toolbar and show you what you can do with it (something to get you started, anyway).
Visibility is where it is
Actually, there are two cool things about this new toolbar.
- The first is that it is now easier to use these external tools than before (more on that below).
- But the second reason I love it is that it creates a "discoverability" about the existence of external tools for people who may not have realized they exist.
When something is up front and visible, curious people seek to learn more. When some feature is not visible, it is impossible to know what you do not know. This is the main reason I HATE the fact that Microsoft hides the Power Query application in a nondescript 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 old Microsoft. Hide the good things so you can't find them. Same problem with Power Query and Power Pivot before. 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 development program that Microsoft has implemented (the same Daniel who wrote Tabular Editor). I think I'll ask Daniel to include some "more info" links in this toolbar so that people with curious minds can find more information about external tools.
Edit: I spoke with Daniel. Apparently there is already a help button on the help tab! Well, I didn't see it, did I? See my previous spiel on Discovery 🙂
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, with which I am still not very familiar.
The ALM Toolkit was developed by Christian Wade (now working at Microsoft). This tool is used to control the implementation of tabular data models in SSAS tabular databases, including Power BI Premium. I will not cover 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 installed one or more of these applications first. Each external tool is a separate piece of software that you must install on your PC first. 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, but I could only see Tabular Editor (as you can see above). Note that the Tabular Editor was grayed out and failed to start. So the next thing I immediately did was update both 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).
When installing Tabular Editor, be sure to select "Create program menu shortcut" during installation. At one point I installed an update without these settings and couldn't figure out how to start the program from the Windows menu 😊.
When I installed DAX Studio I got this warning
I just clicked the "More info" link and then "run anyway." This software is perfectly safe as long as you download it from the official site.
After starting Power BI Desktop again, I was able to see both DAX Studio and Tabular Editor; both were dimmed.
Once I opened a workbook with a data model, both were activated and clickable. I was chatting with other PBI professionals the other day and they reported that others had a similar experience. The funny thing is that now I no longer have this problem. Applications are always available and are not grayed out.
What can I do with DAX Studio?
I'm glad you asked. A lot actually. Let me give you one thing you can do about which I have not written before. A relatively new feature in DAX Studio is the Vertipaq Analyzer integration (developed by SQLBI.com).
I opened an existing Power BI workbook in Power BI Desktop and then clicked the DAX Studio start button on the External Tools toolbar. I loved how quickly it launched DAX Studio and directly connected DAX Studio to the data model running on my PC (running inside Power BI Desktop). Then I switched to the Advanced menu within DAX Studio and clicked "View metrics". This is the Vertipaq Analyzer integration.
There is a large amount of information available quickly and immediately, as you can see 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 manage the size of your model and potentially improve performance (these 2 things are directly related). For the purpose of this demonstration, I am not getting any value from the sales order number. I went back to Power BI Desktop, went to the sales table (# 1), right-clicked the SalesOrderNumber column (# 2), and then deleted it (# 3). Then I saved the workbook.
The next step is to update the metadata in DAX Studio and then check the statistics again.
The column is gone and Sales CustomerKey is now the largest column (not something I can do much about).
In my very simple Adventure Works DB, I saw a reduction in the file size of the 18% with this action.
There is much more you can do with DAX Studio and you can find many articles on my blog.
What can I do with the Tabular Editor?
I discovered Tabular Editor relatively recently and have written a few articles on that as well. I have a tip below that I hope will help you. But first, I want to point out that when I launched Tabular Editor, the experimental features warning still exists.
There are functions in Tabular Editor that are not supported by Microsoft (at the moment). You can enable or disable this feature in the Tabular Editor preferences.
Now one thing to keep in mind. I activated the "unsupported" functions in Tabular Editor a few months ago. Back then, it was not possible to write back to an open data model running in Power BI Desktop unless this experimental feature was enabled within the Tabular Editor. Now I have disabled the unsupported functions within the Tabular Editor, but can still save to the data model directly from the Tabular Editor. I recommend that you leave it off (or turn it off now if you previously turned it on). That way you will be safe.
Enhanced metadata
But there is one more thing. If you want to use the Tabular Editor in the way that I describe in my previous blogs, then 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 keep in mind that my friends tell me that this preview feature is definitely "preview", and some folks have reported catastrophic failures with this setting turned on. So turn it on, yeah; but be sure to keep incremental backups of your model if you do. Also read the release notes on this preview feature available from the preview settings.
Enhanced format strings
One thing you can do out of the box with the Tabular Editor that is currently not fully featured in Power BI Desktop is control the format strings of the values. To prove the point, note below that I have a measure that can return a positive or negative currency.
I went to the new External Tools toolbar in Power BI Desktop and opened the Tabular Editor. As with DAX Studio, Tabular Editor launched immediately with a direct live connection to the running data model on my PC. Using the 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 just changed the string to something I needed, much more flexible than Power BI.
$ #, 0; ( $ #, 0); $ #, 0
Then I 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 want the brackets for negative numbers." So I changed the format as follows
#, 0; (#, 0); #, 0
Saved again, and I have this
Wrap
These are just 2 very simple things you can do from the External Tools toolbar. For more information, explore my blog articles on DAX Studio and Tabular Editor.
