Move All Columns into a Display Folder with Tabular Editor

This is my third article about Tabular Editor, the others being the recent introduction article a few weeks ago, an also an article explaining how to copy measures between PBIX files. I watched the excellent webinar series organised by my friend Mike over at PowerBI.tips where Daniel gave a more in depth overview of Tabular Editor over several sessions. This series inspired me to have a go at writing my first Script using Tabular Editor, and I thought I would share what I learnt with you here.

What I Learnt

This article will not be a blow by blow description of how I learnt some things.  I made a few mistakes along the way, and also note that I am not a programmer, rather I will just give you the key learnings to help you take the same journey with less pain if you would like to do that.  I am approaching learning this new scripting language the same way I taught myself Excel VBA.  It feels very similar to me.

Key Learnings

• You no longer have to save your PBIX file to a PBIT file before editing (although this is still unsupported for now).
  • First turn on the enhanced metadata setting in the preview features. I showed you how to do that in my introduction article linked above.
  • Save a backup copy of your PBIX file (you have been warned – this is currently not supported).
  • Open the PBIX file using Power BI Desktop on your PC.
  • Open Tabular Editor.
  • Instead of opening a file (like you do when accessing the pbit), you simply connect to the model as shown below.

    

• Tabular Editor scripts are case sensitive, so model.allcolumns is not the same as Model.AllColumns. This is the same with the M language, so I am actually already familiar with this concept – as long as you know, I guess.
• There is only limited Intellisense for writing the scripts.
  • The implication is that you will need to know the scripting language in order to write a script. For my first script, I just looked at the sample code that Daniel has loaded here and also what I saw in the webinar series. This was enough for me to work it out.
  • One thing I did work out (and from the webinar) is if you drag an object into the script editor, it will create some (correct) coding reference to that object.  eg, I dragged a column into the script editor below.
    
  • If I then deleted everything after “as” and added a full stop (period), the Intellisense worked quite well.  This was the best way I was able to find the code I needed to solve my problems.
    
• The Display Folder property is only available in the latest version of Tabular Editor. I couldn’t work out why I couldn’t set the display folder like Daniel did in the webinar. On a hunch, I installed the latest version (even though mine was only a few weeks old) and that fixed it.
• When you type a backslash inside text, it is used as an escape character. If you want to actually use a backslash, you have to type 2 backslashes \\.
• You have to finish a line of code inside the curly braces with a semicolon;

With all that background, let’s move onto my first script.

My First Script

When watching the webinar, Daniel showed how you could move measures into display folders. I wrote a series of articles about using display folders in the past, with the most important one (in my view) being the one where I recommend using column display folders instead of measure display folders. You can read why I prefer column display folders over measure display folders in the linked article above. So it occurred to me that moving all columns into a display folder should be the first thing to do with any new model, and that sounds like a great opportunity to write a script (there is a second bonus script at the end of this post too).

As you can see below, my first script is very simple.

foreach (var c in Model.AllColumns)
    {
        c.DisplayFolder = "_Columns";  // use double backslash for subfolders, e.g. “_Columns\\subfolder”
    }

The first line is the loop construct foreach. I am familiar with this as I have used it before in Excel VBA to loop over collections of objects, such as for each Cell in Cells.

Then the code Model.AllColumns allows me to loop over every column in the data model without the need to first select each column manually. Alternatively I could replace this code with Selected.Columns and that would instead make the script work on the columns selected in the UI.

Then the actual code (line 3) is very simple. It takes each column one at a time, and then sets the DisplayFolder property to whatever you have in the quotes.

Hide Columns in Fact Tables Used in Joins

Here is a bonus second script.  One good practice is to hide any column in a fact table that participates in a join.  Consider the ProductKey column.  This column exists in both the product table and the sales table.  Once a relationship has been created you really should use the ProductKey from the Product table, not the one in the sales table.  That is why you created the relationship in the first place.  The following script does that automatically for you.

foreach (var r in Model.Relationships)
    {
        r.FromColumn.IsHidden = true;
    }

Any Comments?

I hope you liked this article and I hope it has inspired other lay-programmers to have a go (you know who you are, the people that like hacking away at Excel VBA code). I would love to hear of ideas for other scripts in the comments below. Chris Haas already made a great suggestion in my previous article. That is to be able to iterate through the values in a column and write measures with hardcoded values, such as

CALCULATE([Total Sales],Product[Category]=”Bikes”), etc.

I really like this idea and will be investigating that in the future as I teach myself this tool. If you know how to do it already, you could post that script below