Introduction to Tabular Editor for Business Users - Excelerator BI

Introduction to Tabular Editor for Business Users

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.

Tabular Editor 1

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

Tabular Editor 2

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

Tabular Editor 3

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

Tabular Editor 4

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)

Tabular Editor 5

Now if you expand the tables as shown below, you will see the columns (#1 below) and the existing measures (#2 below).

Tabular Editor 7

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.

Tabular Editor 8
If you look at the script above, you can probably work out what it does, even if you are not a programmer.
Go to the Tabular Editor window, click on the Advanced Scripting tab (#1 below) and paste the copied code in the script window as shown below.
Tabular Editor 9
To save the sample script, click on + (#1 below), give a name to the custom action (#2 below) and check the box “Column’ (#3 below).  Checking the box 3 ensures that this script only gets executed against a selected column and nothing else.
Select Columns
If you need the script at a later time you can use it by clicking on Samples (#1 below), click on Custom Actions (#2 below) and then on the name of the script (#3 below).
Tabular Editor 11

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

Tabular Editor 12

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

Tabular Editor 13

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

Tabular Editor 14

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 15

Tabular Editor jumped into action and automatically created the set of Time Intelligence Measures (#1 below) based on the measure Total Sales.

Tabular Editor 16

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

Tabular Editor 16

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.

Create 12 Time Intelligence Measures In 10 Seconds

What Else?

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.

What the?

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.

23 thoughts on “Introduction to Tabular Editor for Business Users”

  1. Hervouet Christophe

    Hello Matt

    I tested all the interactions you show between tabular editorial and a power BI report
    All this is possible, I think by activating the new metadata (allowing xmla read / wite access)
    I also tested the possibility of translation (it works)
    –> https://thebipower.fr/index.php/2020/04/12/automatic-translation-of-tabular-model/

    However, a problem appeared while creating a calculationgoup
    I think it is not yet implemented in powerbi desktop? (…not valid M expression)
    –> https://forum.enterprisedna.co/t/calculation-groups/6224
    (Or should you connect to the PREMIUM work space dataset?)

    Bravo for all your posts

    1. correct. Calculation groups are still not supported in Power BI Desktop. They ARE supported in Power BI Premium, so you can pretty well take that as a sign that it is coming to Power BI Desktop soon. There is no point having that ability in Premium if you can’t build it in Desktop. My guess is this is a standard deployment step. Both the service and Desktop need the feature, it is just a matter of timing when they get deployed. Now given today is 27th April and we still haven’t seen the April version of desktop yet, my guess is MS has had a few bug issues this month. There is plenty more goodness ahead, I’m sure 🙂

      1. Hi Matt,

        Great content and sharing once again. Sorry to come back to this one so late after the last posts but I get the “not a valid M expression” error when editing the PBIT file using Tabular editor. I could not follow the steps in this article without the error.

        Any help would be greatly appreciated…

        Thanks

        Matt

        1. Hi Matt. I’m not sure what is going on here. Tabular Editor doesn’t do anything in Power Query, and this is a Power Query error. Maybe take a look at my later article where it shows how to connect to a running PBIX and not the PBIT. Make sure you take a backup copy of your file first – some things are not supported and things can break. Otherwise, you have my email already. If you want to send me a video showing what is happening, I will give you my view. Alternatively, look at my next post

  2. Will there be a “cloud” based version of Tabular Editor? One of the limitations of Power BI Service is that you can’t do DAX in the cloud, so my customers who have Apple products use Tableau instead. If there were a cloud solution to build DAX calculations for Power BI Service, this might solve that issue.

  3. Kristoffer West

    Hi

    One thing that will break the model is if you create new tables in your model.

    Other than that it works like a charm.

    One thing you should include is how easy it is to create folders. And how that works for your scripts as well.

    Great article.

  4. Matt,

    Thank-you for this!

    Your content is always outstanding and this tool looks exceptional. This difficult moment in our history presents a perfect time to introduce more complex topics as we (I am in Italy under lockdown for over three weeks now) bizarrely have more time to work through them.

    It would be great to see Microsoft offer more learning opportunities at reduced prices during this time. Or perhaps they could offer a developer/student edition of premium capacity for a limited period? (The Power BI team have published a great series on Paginated Reports this week – but without the software license, it is tough to work through it.)

    You have taken a strong lead, once again thank-you for everything you publish.

  5. Hi Matt –

    Thanks for making Tabular Editor more accessible!

    I’m interested in creating a handful of CALCULATE() measures based on modifying the filter context of a single column, but iterating through the values of a list. Something like this:

    [Sales (Install)] = CALCULATE([Sales], fact_Data[InvoiceType] = “Install”)
    [Sales (Service)] = CALCULATE([Sales], fact_Data[InvoiceType] = “Service”)
    [Sales (Contract)] = CALCULATE([Sales], fact_Data[InvoiceType] = “Contract”)

    Can a script be written where I pass in the list {Install, Service, Contract} and iterate over that?

    Follow-up….can I dynamically create that list based on evaluating a DAX expression…like VALUES(fact_Data[InvoiceType])?

    1. Kristoffer West

      Yes you can do this, but be careful with the references.

      I have found that the thing you are looking for, is not really worth the initial work, since it will be different from model to model.

      The scripts of time intelligence are great because the will be the same across many models.

      1. Hi Kristoffer –

        Would you mind sharing a link to the method?

        I understand it will be different from model to model, but it should be minor tweaks to a pattern.

        I have 112 values I need to iterate over, which is likely to triple.

        Best,

        ~ Chris

  6. Hi Matt,

    Thanks for the write up on Tabular Editor. How does this stack up against DAX Studio? It would be nice if there is some comparison of both tools.

    Thanks again for the wonderful article.

    Regards,
    Eddie

    1. Matt Allington

      Thanks Eddie. The short answer is that DAX Studio is a query tool For tabular models – read only. Tabular Editor (as its name suggests) is an editing tool for tabular models. It has write back capability.

  7. Thanks for this – looks very useful. I have tinkered Tabular Editing by connecting to an open PBIX file – it’s been useful for exploring and documenting a data model, but I’ve never been able to write-back to it. The PBIT method isn’t working for me though – for everything I try it on I get the same error message:
    “Error: Unrecognized JSON property: sourceQueryCulture. Check path ‘model.sourceQueryCulture.”
    Any ideas what that means and how I get around it?

    1. Matt Allington

      Only a guess, but the error says “culture”. Do you by chance use ; instead of , for separators? I think I remember Daniel saying that under the hood, a PBIX file stores everything as a ,

  8. Thank you very much for sharing. I have heard the Tabular editor but I’ve never worked with it. It is amazing what you are showing and what this software can do. I am looking forward for your next articles concerning Tabular editor!
    Thank you once again!

Leave a Comment

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

Scroll to Top