Blog

Writing Tabular Editor C# Scripts like a Boss

A few months back I wrote an article of what I thought would change the way I (and many more I thought) would write c# scripts from now on. In that article I explored the possiblity of creating a custom dll with all the classes and methods that would make my scripts shorter, more robust and more sophisticated all at once. The idea was cool but not quite convenient nor for development nor for distribution. However something good that came out of that is that my colleague showed me it was possible to have really good intellisense for Tabular Editor C# scripts inside Visual Studio! So even if you are not thinking about custom classes, you should definately be thinking on Visual Studio as your home for actual coding. Today I want to talk only on how to set it up so that it will not let you do anything that Tabular Editor 2 will not accept.

The process is far from intuitive, but it needs to be done just once, and the payback is huge IMO, so bear with me. Some of the steps will be a repeat from my original article, but I wanted to write it once again for clarity and to include a few enhancements I have found along the way and some tips that will help you get started. If the article gets too long the tips will probably go in a different blog post.

Before we get started, I know that Tabular Editor 3 does have pretty good intellisense for c# scripts, but it does not have all the bells and whistles (like automatic null checks, code hints, it even has that copilot thing!) that make Visual Studio a tool for programmers (which makes total sense). Also there are some bits that are left out of the intellisense — you don’t get intellisense for dlls that you load, or in-script classes, which are cool and much faster to develop and distribute as they live together with the script. Purists will look at them as an abomination, but it is quite convenient. Anyway, for these, you can get intellisense in Visual Studio, but not TE3 at this point in time.

Get Tabular Editor 2

Anyway, first you will need Tabular Editor 2. Since we are going to refer to files of the program we have to use TE2 not to infringe any EULA. Also we are focussing here in producing something that can be consumed also by TE2 users so we need to test that indeed runs. Grab the latest version from Github and install it right away

Get Visual Studio

Next we will need Visual Studio. Some people tend to perfer VSCode, but I think that the aids to program are better in Visual Studio so I’ll stick with that. I may end up a section on what is different for VSCode. Go to visualstudio.microsoft.com and download the community version or higher if you have a licence for it.

Install an important bit of Visual Studio

Visual studio is so huge that it does not install everything, you need to chose. Doing this is still huge so imagine. We need to make sure we install the .Net package to be able to create the type of projeect we need. You can do this during intallation or afterwards with Visual Studio Installer, which is like a separate application in your pc to update and modify the installation of Visual Studio.

Create a Class Library (.Net Framework) project

We now select the option to Create New Project and filtering a bit by «Class Li» you should already see the option to select «Class Library (.Net Framework)» next to an icon that says «C#» . I know it’s a bit scary, but remember, we just need to do this once and enjoy forever.

On the next dialog it asks us for a project name, folder and framework version. To be honest I’m not familiar with the fine details, so I’ll just go with 4.7.2. After all, we are not going to really execute much, we just want to get the intellisense and aids to write code.

Let’s teach TOM to our environment

Like in the movie The Matrix, where you can learn jujutsu by loading some stuff in your brain, here we’ll make this project suddently know about all the objets and properties of the Tabular Object Model, aka TOM. In order to do that we’ll add 2 project references.

On the «Solution exporer» on our right hand side, right click on the project name, go to «Add» and then select «Reference» in the submenu.

Now we go to Browse on the left hand side, then click browse button, and then on the file navigation bar type

%APPDATA%\..\Local\TabularEditor

This will navigate to an actual folder in your computer where you’ll find TOMWrapper14.dll which is the file we need. Select it and click «Add».

Now repeat the previous step with the following path

%programfiles(x86)%\Tabular Editor

and select the TabularEditor.exe application file (file extension might be hidden, but that’s ok).

Now let’s add some code to say that we really want do use TOM

You would think that what we just did would be enough, but it’s not. Now on the code of the Class1.cs file, you see many lines starting with «using» at the top. We’ll add a couple more lines there

using TabularEditor.TOMWrapper; 
using TabularEditor.Scripting;

Now the intellisense already knows about TOM,  but still does not know about an object called Model and an object called Selected. Both are super important in scripts because all referencces are connected somehow to either of them.

Intellisense is now aware of a Class called Model, but no object. We need to define it. Normally objects are variables and variables are lowercase, but Model is somehow special in C# scripts because is already there from the beginning. So we need to cheat a little bit.

But wait, before we continue let’s put some proper name to the file we are edditing. If you change the file name it will offer your to rename the class name in the code which is indeed a good idea.

Ok. So what we’ll do now is define two variables inside the class so that we’ll be able to use them in any method that we’ll include. I call them here methods because that what Visual Studio will think they are, but actually they will be just places where we’ll type our scripts before copying the contents to Tabular Editor. I feel a bit bad for visual studio but hey if anyone knows how to execute the scripts from Visual Studio, be my guest and teach me.

As we said, the objets are Model and Selected. The first one is easy, the second one, not so much.

Model Model;
TabularEditor.UI.UITreeSelection Selected;

after these two we can alrelady start creating this faux methods where we can write our scripts with all luxury of intellsense and tips to write better code. Methods start with whatever they should return (or «void» if they return nothing) then the method name (upercase this time!) and then would come parameters which make no sense in our case, so just opening and closing parenthesis and then the code of the method between curly brackets. Once again, the code of the method is actually our Tabular Editor script, remember. Something like this.

Why is SelectTable an error?

SelectTable is a function you can use in C# scripts that opens a dialog box to let the user select one table which is then returned to a variable. However if you try to use it directly, it gets an ugly red underline, what what is even worse, no intellisense!

Well, to help Visual Studio get this right, we have to put the following prefix to SelectTable (and other functions like Output, Error, Info, SelectMeasure, SelectTabularObject) : ScriptHelper. (the dot too!). Now it works:

Be aware that you should remove all these «ScriptHelpers» before executing in Tabular Editor, at least for now, but it’s something that can be done in a couple clicks with find and replace.

If you don’t want to go through the pain of deleting all the ScriptHelpers, you can do what my fellow PUG Barcelona member Nelson suggested on a recent workshop on c# that I was conducting (teaching is learning, really!). You can create a method with the same name and arguments and then visual studio will think it uses the method and will not complain. However some function like SelectTable can take different parameters so implementation might not be so easy. Treat with caution.

UPDATE(2022-10-08): There’s even a better way to solve this! As pointed out by David, we only need to add

using static TabularEditor.Scripting.ScriptHelper;

at the top and bam! it automatically behaves like Tabular Editor 2 (but with  fancy intellisense)! Awesome tip David!!

UPDATE (2022-10-29): There is only one small-ish caveat! Some of these functions have more than one «overload» that means that you can send different sets of arguments and it will do slightly different things. One of these is «SelectTable». If we only type SelectTable we only see one of the overloads, which accepts only a table to preselect it on the list and a label. However, there is another «overload» that allows you to pass a table collection as first argument, and the list will only show those tables, which is very neat. To be able to do this you will need to use the «ScriptHelper.» prefix. And then something that I learned today. With the cursor inside the parenthesis if you push CTRL + SHIFT + SPACE you can see the different overloads (these also appear right after you type the opening parenthesis).

By the way I have not managed to see the different overloads in TE3. It just mentions there’s the one showing and 2 more. Morale: Keep the using TabularEditor.Scripting; line even if it says it’s not using it. You never know when you will. And if you do use it, remember to remove them!

Ok, just one more thing to close this topic… if you *really* want the ultimate coding environment and you do not want to remove the «ScriptHelper’s» manually every time you copy the code back to Tabular Editor, your only alternative is to follow Nestor’s approach for each of the overloads of each function (or at least those that you’ll use). Something like this

Just remember that you’ll  have to do it for all the overloads you want to use, even the default one you were seeing since this methods somehow override what comes from the directive.

Make Visual Studio *think* like Tabular Editor 2

You may think we already did it all, and mostly we did. However there is a small thing that we should take care of. As we type away and see no red lines we might assume that this will execute flawlessly in TE2. Well I have news for you.

Consider the following script:

Looks beautiful, doesn’t it. No errors at all. Well, let’s try it out in Tabular Editor 2

What is going on??

Well, the error messages are not that helpful here. What you need to know is that Tabular Editor 2 only accepts (by default) c# version 6.0. 5.0 (which is the version that ships with .Net 4.7.2 Framework) I’m not completely sure what version is Visual Studio using, but it’s clearly above that. Googling a bit you’ll see that the «is» was introduced in C# 7.0. So the question is: «How can we make Visual Studio validate my code with c# 5.0?» This took some trial and error (and invaluable help from my colleage Xavi Paterna), and here I’ll share the only method fool proof that has worked for me.

Go back to Visual Studio, save your project, right click on the project name and select unload.

Don’t panic. You’ll see something like this

Once you recover, you need to add the following code after some closing tag </PropertyGroup>

<PropertyGroup>
   <LangVersion>5</LangVersion>
</PropertyGroup>

(the image shows 6 but apparently there's little difference between the two)

Once you do this, save your project, right click on project name and select «Reload Project»

Now double click on TE_Scripts.cs again and after a few seconds you will see this

Now we see that we have a problem there. Visual Studio suggestion to fix it is to increase the language version which is not an option for us, so you just need to know that instead of «is» you need to use «==».

And when we go to Tabular Editor…

Congratulations!

And that’s it! With this we already have a base environment in which to write beautiful C# scripts with awesome intellisense and aids such as «this variable does not exist in the current context», «this variable is not being used» etc etc. After all Visual Studio is to write code, so we get all the goodies while developing scripts for Tabular Editor, which are like Super Powers for power bi.

Remember to come back to your project every time you want to write some code — You can just create a new method for each script or just delete and use it as a whiteboard, your call. I find it useful to be able to quickly revisit other scripts without leaving Visual Studio. In future posts we’ll cover some strategies and tips to get user’s feedback on what is to be done or which table, column etc has to be used.

Continue the conversation on Twitter and LinkedIn

PS: You are trying in your machine and things don’t flow as expected? Come to Reddit and let us know where did you get stuck!

if you prefer to do it in Spanish we also have a subreddit for that