Blog

Bringing Tabular Editor c# scripts to the next level

Is no secret that if you work daily with Power BI, you should be using Tabular Editor,  but if you are working with lots of different datasets you probably feel like you are doing the same thing over and over again. Then it’s time to bit the bullet and get your hands dirty with Tabular Editor scripts. If you do, don’t start with a blank sheet. Always copy from someone and build from there (that’s what I did!) — there are lots of great scripts out there. Maybe not tons, but certainly lots.

But anyway, once you get going with Tabular Editor C# scripts (now we need to specify if we are talking c# or DAX scripts) you may feel that you are repeating code, and as in any kind of programming, that’s not just a waste of time, it’s bad practice. So, today I’ll share how I’m starting to move my scripting to the next level, creating a custom DLL for Tabular Editor C# Scripts (never did that before!) and making use of intellisense by moving development (or at least the bulk of code typing) to Visual studio.

First of all, great shoutout to Daniel Otykier and Xavi Paterna for bearing with me and my lack of C# expertise, and helping me put the pieces together. To bring some context to this post, I have a Excel+VBA background, working basically in excel and vba for like 7-8 years. I started barely knowing how to write macros, but by the 4th year or so I had developed some code management tool in which I could load and publish (to a network folder, no real git or anything) code modules from any file. While this took some time to set up, it later sped up my development times so much that I still use the approach every time I need to do almost anything in excel. It went together with an add-in toolbar that gave me super-powers to do stuff, and the ability to modify it anytime as I had the code behind it.

Fast forward a few years, and I’m now all-in Power BI, loving it but also hating doing stuff manually over and over again. I was so happy when I started doing scripts to industrialize calculation groups and other little things (but basically calc groups). However, I felt that the programming I was doing was far away from what I used to do in VBA, where I had my functions which I could keep extending forever with further functionality. Something as basic as opening a workbook can be made much more functional if you add options such as check if the file is already open, or open it but hidden, or open it in a hidden instance with macros disabled, and in any case if anything goes wrong give me a nice error message and not the VBA screen that freaks out users.  Could we replicate something like that for Power BI?

Well, the need to encapsulate logic in functions is there. Something as basic as creating a calculation group, certainly can be more sophisticated than the basic function of the TOMWrapper. Something as basic as the name of the calc group needs some thought. I want my scripts to be  «one-click compliant», that is, there is no configuration done on the code itself, it can be stored as a macro and any configuration happens on runtime through dialogs. But then what to do you with names? It’s good to have default names for the things you want to create, but also leave the opportunity to modify the name if the user prefers a different name. But either way, maybe that name is invalid because there’s already another table with that name. And you don’t want the C# error freaking out your potential users (or people who are looking while you execute your own macro). This is something that you’ll  face every time you create a calc group (or a table, or a measure etc). So how can we encapsulate all of that in the same place?

I started by bugging Daniel on the Tabular Editor 3 Issues forum on github where he gave me some tips. Then with Xavi and (and some more bugging to Daniel) and some trial and error we got it to work. I’ll try to replicate here what I did if anyone wants to give it a go. I’ll publish that on github (once I solve some weird git errors I’m getting) so feel free to fork it and extend it or send pull requests.

Getting a development environment

First of all you’ll need Visual Studio (community edition is fine). During the installation, or afterwards using Visual Studio Installer -> Modify. Make sure you install the .Net framework. I think it’s enough selecting options on the «data storage and processing» workload box, but if you want to be extra-sure install the .Net desktop development. As you can see I have barely any idea of what I’m doing.

Anyway, you’ll know you did the right thing if you can open Visual Studio and create a «Class Library (.Net Framework)» project

First steps building a repository of functions

From here, I’ll try to recap everything we did with Xavi. By default it will create a Class1.cs file, wich we can rename to whatever we want to do (file name and inside, where it says Class1).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExtendedTOMWrapper
{
    class Class1
    {
    }
}

First of all I wanted a very simple function to initialize strings. It seems an overkill, but it is not, because that’s what we’ll use to get the name to be used for calc groups, measures everything. We have a default name, but also potentially a list of invalid names, or checks to do that the name does not match any other measure or table, depending on what we are building.

But first things first, we are just building a repository of functions, so it’s not like we are building instances of the same things (which is what most tutorials on classes do, a car with many attributes, you name it), here we just want to have some functions that can be called. So we’ll group functions in different classes depending on what they deal with. You don’t have to do that, but it’s good practice. All functions dealing with strings will go under StringUtils.cs (start with capital letter!), calculation group functions in CalculationGroupUtils.cs, you get the idea. And we need to specify that there are no different instances of it. For our StringUtils.cs we’ll start with something like

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExtendedTOMWrapper
{
    public static class StringUtils
    {
    }
}

My goal is to have functions that return true if all is good and false otherwise. Of course they will have also output parameters, but the function itself will return false if there’s a problem. Then my tabular editor script will have some lines that look like

if(!myUtils.myFunction(...)){return;};

Here we’re just saying, launch the function and if something goes wrong just end here. Any warning or error messages will be encapsulated in the function. If the user closed the dialog to provide a name for a measure, you can just show a message then saying «invalid name» or «you just aborted the process» or something. You do it once, you can call it from any script.

I know! you might be wondering, how I’m going to call these from tabular editor??! don’t worry, we’ll get into that (and we’ll do exactly as Daniel said in the issues forum post). Before we get there, just some things I realized when building the first functions.

Adding a reference to TOMWrapper

If you don’t know what TOMWrapper is, is a bunch of functions that Daniel Otykier created to manipulate the Tabular Object Model (hence the TOM) more easily. I’m not sure what comes from TOM and what comes from TOMWrapper, but you just need to refer to TOMWrapper which already brings a lot of references built in to make your life easier.

So we need to refer to TOMWrapper. But we need to do it first at a project level and then at a class level (don’t ask me why).  To do it at a project level, we’ll right click the project name on the solution explorer and select «Add Reference»

Now we’ll need to tell Visual Studio where the TOMWrapper.dll is. So we’ll click on Browse (1), then on Browse again at the bottom (2), then we’ll go to Program Files –> Tabular Editor 3 (3), select TOMWrapper.dll file (4), click Add (5), select it from the list if necessary (6), and click OK (7). Piece of cake.

While writing this I wonder what will happen when running this in other machines. Maybe I’ll need to release a Tabular Editor 2 version? What if it’s a portable version?? Oh well, time will tell. At this point I’m happy it runs on my machine.

Anyway, all this was to add the reference at project level, if we want to use any of the functionalities of the TOMWrapper inside a class, we’ll need to specify that at the beginning adding the line

using TabularEditor.TOMWrapper;

Then we’ll get all the good stuff of the TOMWrapper plus the intellisense of visual studio! how cool is that? But not so fast. If you just use Model in your code (basically to get any information or do any modification to it, which is the whole point of doing a script), visual studio does not like it. It’s like, «what model are you even talking about?» kind of thing. Indeed, if we want to do stuff with the model inside a function, we’ll have to pass the model as argument. Getting a bit (more) nerdy, we’ll pass reference to an object of the class Model. But then inside the code of the class, we do not refer to Model (with capital M) but rather the variable model (lowcase m) that of type Model that has been passed. I know, I hate it too.

Anyway, I’ll just paste here how the StringUtils.cs is looking like right now. Important bits include:

  • public static bool InitString
    • public: can be called from outside
    • static: there’s just one instance, not like cars in a game or anything like that
    • bool: that’s the type of whatever the function returns. C# is very picky with types. Remember to put what is returned in all possible logical paths.
    • InitString: this is the name of the function. Start with Uppercase or c# lovers will not even look at you.
  • From the argument list
    • start with all the arguments that are not optional. I tend to put a default value for all that possibly can so I can build up code fast and then refine it wherever necessary.
    • prefix «out» to arguments that bring changes back to the main function, equivalent to ByRef in good old VBA
    • type comes first, then argument name (start with lowercase!)
    • remember to include the Model model argument to be able to access tabular model properties and methods
using Microsoft.VisualBasic;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using TabularEditor.TOMWrapper;


namespace ExtendedTOMWrapper
{
    public static class StringUtils
    {
        public enum CheckType
        {
            Table,
            Measure,
            Column,
            None
        }
        public static bool InitString
            (
                out string stringToInit,
                string label = "Name", 
                string defaultValue = "Default Name",
                string errorMessage = "No name provided",
                string invalidNameErrorMessage = "Already another instance with the same name",
                string[] invalidNames = null,
                Model model = null,
                Table table = null,
                CheckType checkType = CheckType.None,
                string prompt = "Default Prompt",
                string valueOnError = "",
                bool promptUser = true //if false, validate only default name is not forbidden, ask for input if it is
            )
        {

            bool validName;
            string userValue;

            do
            {
                if (promptUser) { 
                    //get name from user
                    userValue = Interaction.InputBox(prompt, label, defaultValue, 740, 400);
                } else {
                    //bypass interaction
                    userValue = defaultValue; 
                }

                switch (checkType)
                {
                    case CheckType.None:
                        //nothing to check all is good 
                        validName = true;
                        break;

                    case CheckType.Table:
                        //if it's going to be a table, there cannot be any table with the same name
                        validName = !model.Tables.Any(x => x.Name == userValue);
                        if (!validName) { 
                            MessageBox.Show(invalidNameErrorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                        break;

                    case CheckType.Column:
                        //if its a column, there cannot be any other column on that table with the same name
                        validName = !table.Columns.Any(x => x.Name == userValue);
                        if (!validName) { MessageBox.Show(invalidNameErrorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
                        break;

                    case CheckType.Measure:
                        //if its a measure, there cannot be any measure with the same name, or any column on that table with the same name 
                        validName = !table.Columns.Any(x => x.Name == userValue) && !model.AllMeasures.Any(x => x.Name == userValue);
                        if (!validName) { MessageBox.Show(invalidNameErrorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
                        break;

                    default:
                        validName = true;
                        break; 
                }

                //if we got a valid name so far check it's not in the invalid name list
                if (validName) { 
                    //if no invalid names then.. 
                    if (invalidNames == null || invalidNames.Length == 0)
                    {
                        //..all good
                        validName = true;
                    }
                    else
                    {
                        //otherwise check if the name is any of the invalid ones
                        validName = !invalidNames.Contains(userValue);
                        if (!validName)
                        {
                            //tell user this name cannot be used.
                            MessageBox.Show(invalidNameErrorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }

                if (!validName)
                {
                    //enable user interaction to fix it
                    promptUser = true;
                }


            } while (!validName);   
            
            //emptystring is a valid string but needs to exit 
            if (string.IsNullOrWhiteSpace(userValue))
            {
                MessageBox.Show(errorMessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                stringToInit = valueOnError; 
                return false;
            };

            //if we reach this point then is all good
            stringToInit = userValue; 
            return true;
        }
    }
}

I have my class, now what?

once we have something that might be useful, it’s time to build the dll. First of all we’ll do a little configuration on how the dll should be built. I have not tested all the possibilities, neither do I really understand the differences, so your mileage may vary, but the way things are working for me is with Configuration = Debug and Platform = x64. What is this really? Not sure, but it affects how the dll is built. You can change it through the Configuration Manager or just on the toolbar.

Now it’s time to actually build the dll file we’ll reference from Tabular Editor script. To do it, right click on your project (not your solution, nevermind) and click «Build»

On the output window on the bottom of the screen you should see some message saying that it succeeded

========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========

Ok, so where is it? That’s something that puzzles me to this day, so bare with me. You have to right-click your project and select «Open folder in File Explorer»

Ok. Whatever. Now what. Now you have to navigate three levels deep to get it. Go to Bin –> x64 –> Debug and bam, there it is! You will find it among other files that I don’t even know what they do there.

Referencing the new dll from a Tabular Editor script

Now is when things get a bit fuzzy, at least for me. In theory you should be able to reference this very same file, by copying the full path in quotes. However, to play it safe, Daniel recommends copying it to the same folder as Tabular Editor 3 (I guess you can replace «Tabular Editor 3» by «Tabular Editor 2» throughout this article and it should all work just as fine — I did not test it though). I also recommend closing and opening tabular editor, even though Daniel says it’s not necessary. I have no proof nor doubts that it’s needed, however. Many times I posted all that did not work on my side only for daniel to say that it does work fine, and then I go back the very same dll and very same script and now magically all works fine. Anyway, eventually it does work!

To do it you need to add two lines of code at the very beginning of your script.

If you use an arbitrary path you need to include it all in double quotes

#r "C:\Users\berna\OneDrive\Documentos\GitHub\ExtendedTOMWrapper\ExtendedTOMWrapper\bin\x64\Debug\ExtendedTOMWrapper.dll"
using ExtendedTOMWrapper;

If you put the dll on the Tabular Editor 3 folder, you just need to specify the name of the dll in double quotes

#r "ExtendedTOMWrapper.dll"
using ExtendedTOMWrapper;

Very important! the first line does not end with semicolon. The second one it does. First one includes extension. Second one does not.

Here as a proof that I am not making it all up, a small video showing how to initialize a string with a default value, letting the user modify it, and being sure that the final one is not any of the existing table names.

It gives feedback on why the name is not good, and let’s the user try again until a good name is found or the user aborts the process by cancelling the input or introducing an empty string as name. This is using the class shown above.

Am I supposed to remember the whole list of arguments?!

That whas the feeling when trying my brand new dll. Also it felt weird, because now that I had coded a bit in Visual Studio, I know there was a brighter world out there with great intellisense. Is there a way I can get to at least code in Visual Studio and then copy paste it to tabular editor for execution?? I asked Xavi one more time, and he pointed me in the right direction in no time. Let me walk you through the –reasonably easy– steps.

We’ll set up a separate project but inside the same solution, so we can go back and forth from the code to the dll code. So we right click on the solution this time and select Add –> New Project

Since we’re not going to really use the project as such, we may go with different options, but we chose to go with one more «Class Library (.Net Framework)» one. Now the important trick is to set the references right to get the intellisense thing moving. For this project we’ll set a reference to our DLL and also to TOMWrapper. It should be enough to refer our DLL, but for some reason it did not work without it. Anyway,  the point is that in the end it worked so let’s go on.

As we did before right click on references of the new project (PlayGround or whatever you have called it) and now go to the «Project» section and select your first project in the same solution

Now repeat and go to the browse section and select TOMWrapper once again

We are almost done I promise.

In the Class1 file (i didn’t even bother renaming it) we did a couple things: We added the using ExtendedTOMWrapper and using TabularEditor.TOMWrapper. But there was one thing that still bothered me,  and it was that I could not access the model calling it Model (with capital letter). Then Xavi suggested defining a variable before the dummy procedure, and to do that with a capital M! something that breaks c# developers code of honor, but it was for a good cause. Then inside the dummy procedure, if I wrote Model followed by a period, all sorts of awesomeness ensued….

And not only that, I could call the methods of my class in all luxury, look at this!

 

sooooo bananas. So awesome that I had to tell the world before actually using it for real. But there was one thing that I wanted to get in place before going further…

What about versioning of the dll?

Before running a script, I want to make sure that the dll being referenced was updated enough (I will never make breaking changes!… or that’s the idea at least). How can I introduce verions and check version and compare stuff?

This is done with another class file that deals with the version of the own dll file. The first method just returns the current version, but the second one CheckMinVersion is the cool one. You pass a minimum version as string, and it will return true if the referenced dll is equal or above that version. Otherwise it’s a no-go and the may process should end. I will enhance it further to show some message and somehow drive the user to the github where to update it or so. Still working on the details.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ExtendedTOMWrapper
{
    public static class VersionUtils
    {
        public static string GetVersion()
        {
            return Assembly.GetExecutingAssembly().GetName().Version.ToString(); 
        }

        public static bool CheckMinVersion(string minVersion) 
        {
            var vMinVersion = new Version(minVersion);
            var currentVersion = Assembly.GetExecutingAssembly().GetName().Version;

            return vMinVersion <= currentVersion; 

        }
    }
}

That’s pretty much it! I think it can be something awesome if I can make it to work more or less in any machine without requiring install permissions, which on paper should not be necessary. I worry a little about the reference to TOMWrapper, but we’ll see if there’s a problem at all and how can be fixed or tamed. Something like that worked wonders for me in VBA, so I have great expectations on this project.

Regards and thanks for reading!