Tabular Editor is a community Open Source software project written by Daniel Otykier. I have known about this product for several years but never really invested the time to understand what it does and how it can help me do my job. Last week I attended the Microsoft Virtual MVP Summit live from Redmond, USA. During the conference, Daniel gave a presentation on how Tabular Editor works, and I have to say I was amazed. Many professional tabular modellers will already know about this tool, but I think many of my reading audience will not. This article is of course for everyone, but particularity my readers who may not know about this product.
What can I do with Tabular Editor
In short, lots of things. I will cover more features in the future. For this article I am going to show you how to automate the creation of measures so quickly that it will make your eyes water – believe me. Just don’t tell the boss how quickly you can do it or you might end up with more work.
Limitations of Tabular Editor with Power BI
Currently Tabular Editor can only operate over Power BI Template files (more on that later). It has the ability to write back changes made by Tabular Editor back to the template file, however be aware that this is not currently supported by Microsoft (back up first). Despite it not being supported, Microsoft has announced that it will be supporting such features in the future. In fact with the March 2020 release, Microsoft introduced a preview feature “Store datasets using enhanced metadata format”. This new data format is part of the journey to re-plumb the back end to support XMLA read/write for management of Power BI datasets.
I can’t wait to see what the future holds in this space. OK, so let’s get started already!
Download and Install Tabular Editor
Firstly download the latest version of the Tabular Editor from Github here https://tabulareditor.github.io/
Install the tool on your PC – you may have to override any security warnings by Windows defender about the publisher being “unknown”. Daniel is a good guy, so you can proceed with the installation with safety. You can then create a shortcut to the TabularEditor.exe file and pin it to Start for easy access. Make sure you leave the option to add a shortcut checked during installation.
After installation, run Tabular Editor and take a look. It looks like this.
Enable the New Metadata in Power BI Desktop
You will need the March 2020 or later release of Power BI Desktop. You can check the version of your Power BI Desktop from the menu – Help -> About. Once you have the March 2020 or later version of Power BI Desktop, you need to enable the preview feature. Go to File -> Options and settings -> Options. Then click on Preview Features (#1 below) and check the box to enable the feature (#2 below).
Close and restart Power BI Desktop to make the feature active. Strictly speaking you don’t need to do this to use Tabular Editor in the unsupported way using the template file. But then again, why wouldn’t you use it given this is the future.
Creating a Power BI Workbook Template File
In order to utilise the functionality of Tabular Editor with Power BI Desktop, you need to save your Power BI workbook as a Power BI Workbook Template file. For illustration, I have taken a Power BI Desktop workbook (.pbix) with a basic version of Adventure Works data model loaded, and saved it as a Template (.pbit) file (#1 below).
This is the file (.pbit) that you can use in the Tabular Editor as of now. The template file contains all the structure of the PBIX file, but it doesn’t contain any of the data.
Opening the PBIT File in Tabular Editor
In Tabular Editor, click on File (#1 below), then on Open (#2 below) and then on From File (#3 below).
Navigate to the saved .pbit file and open it. You might get a warning message as Power BI does not yet support all the Tabular Editor features. Click on OK and proceed.
Starting with Tabular Editor
You can see your model components in the left hand panel. Take note specifically about the Relationships (#1 below) and Tables (#2). As we are going to work with a Power BI data model, I recommend you do the following:
- Show Columns (#3)
- Hide Partitions (#4)
- Show Hidden Objects (#5)
Now if you expand the tables as shown below, you will see the columns (#1 below) and the existing measures (#2 below).
Quickly Creating Measures Using Advanced Scripting
Next, I will show you how you can create multiple measures using the Advanced Scripting feature in Tabular Editor. You can create your own script file or download an existing script file from a script library such as Github, and then rapidly create a number of similar measures in no time at all.
Downloading a Script File
Here is a great link to some useful scripts https://github.com/otykier/TabularEditor/wiki/Useful-script-snippets.
Scroll down to the section Create measures from columns. Copy the code given in that section.
Running the Script to Create Multiple Measures at the Same Time
Now you are ready to run the script on multiple columns at the same time. The script generates measures on every selected column to create totals.
- Select the columns in the left panel with Ctrl key pressed down (#1 below)
- Click on Run Script (#2 below).
In my example, 4 measures (#1 below) were created for each of the selected 4 columns. Note that the columns used in the measures are now hidden (#2 below) – this was also part of the script. Click on a measure and then on Expression Editor tab (#3 below). The DAX formula that was generated for the measure is displayed (#4 below).
So there you go, 4 measures created in a few seconds. How cool is that!
Creating Time Intelligence Measures
Now for something even better. Writing Time Intelligence functions in DAX can be very time consuming, expecially when you have to create sets of time intelligence measures across many base measures, like [Total Sales], [Total Quantity], [Total Cost], etc. You can easily create a full set of Time Intelligence measures across multiple base measures using an advanced script. Here is a sample script that does just this (you can download this from the link provided above).
I copy-pasted the code into Tabular Editor. Before moving on, I changed the reference to the calendar table (it is Date in the script, but mine is called Calendar). When you use these scripts in your data models, you may need to edit them before use. I then saved the script giving the name as Create Time Intelligence Measures.
In the left panel I deleted all the measures apart from the Total Sales measure. Next, I right-clicked on Total Sales (#1 below) and then on Custom actions (#2 below) and then on the script I saved before (#3 below).
Tabular Editor jumped into action and automatically created the set of Time Intelligence Measures (#1 below) based on the measure Total Sales.
Lastly, save the file and exit Tabular Editor.
Using the Measures Created in Tabular Editor in Power BI
Warning: As you are going to use a preview feature that is not yet supported, always keep a back up your Power BI workbook before proceeding.
- Start Power BI Desktop.
- Open the .pbit file that you used in Tabular Editor.
Note that Power BI Desktop connects and then refreshes all the tables from the data source – this is the standard behaviour when opening a template file. You can then see the measures (#1 below) and their values (#2 below).
12 Measures in 10 Seconds
I wanted to produce an animated gif image for this post showing how easy it was. Here it is. 12 time intelligence measures in 10 seconds.
There are lots of other things you can do with Tabular Editor, including migrating measures between workbooks among other things. I would love to hear from people about what their favourite feature is and I may write a future post showing how to do that.
I started to prepare for next week’s blog article – how to copy measures between PBIX files using Tabular Editor. I knew I had done it before but couldn’t remember how to do it. So I went to Google. Look what I found?! https://exceleratorbi.com.au/copy-measures-between-2-power-bi-files/ It seems I already wrote this article a year ago! I think given the interest in this article, I will re-release the copy measures post again next week.