Hello hello, today is C# again. As you know I do like C# scripts for Tabular Editor, as they allow us to automate stuff around Editor, which in turn allow us to do things faster than in Power BI Desktop, so it’s like a super power on top of a super power. In my previous post on the topic, I talked about using a custom class to be able to reuse code, making your scripts, shorter to write, and more robust, as you can put all the bells and whistles once in you custom class and reuse forever. That approach is awesome, but it had still two remaining aspects preventing larger adoption. One is that the set up as a bit of a pain. And the other is that copying the code to tabular editor is not as fast as one would like. You need to select the code of the macro, then the custom class, then fix all the external references at the top, etc. Well, today I’ll talk about a repository that will allow you to start coding in almost no time, and a script that will copy any of the macros of your Visual Studio file *along with the custom class code* and even combining all the external references of both the macro and the custom class. How cool is that?!

As I was starting to write this post I realized I haven’t yet talked here about a repository I put together that saves most of the work I explained in this blog post and this one.

Let’s get started!

Disclaimer: «effortlessly» in the title refers to the end result! We’ll do some work to set it up, but is not too bad. Trust me!

Let’s see the final result first

To keep you engaged on this rather long post, I think it’s worth to see what are we fighting for here:

As you see in the video, coding in Visual Studio is much much better than coding in TE2 (and even TE3). You can see we define first a custom class method that checks if there’s a table with certain name, if it exists it returns the reference to the table,  otherwise it creates a calculated table with the expression provided. Then it goes to write a script that uses the method. Once completed (and saving both files before anything else) goes to Tabular Editor 2 and with the right click menu goes to macros, and selects «Get Macro». Then the name of the script defined in visual studio appears and once selected can go to the «C# Script» tab and paste it. We can see that the script now includes the external references defined at the custom class level (not relevant to this script in particular, but no harm done either) and more importantly it includes the custom class in-script at the end, so there’s no dependency of that script with anything. Anyone can use it.

The script itself is very basic and can be improved in many ways, it’s just to show a simple use case. Once you want to build robust scripts, you find yourself repeating code over and over again, so this approach I think brings us one step closer to C# nirvana.

Clone the repository

First things first. To code in Visual Studio you need… to install Visual Studio. Community is fine. Just make sure to include the .NET module in the installation.

Then go into Visual Studio and select «Clone a repository»

Now we need to provide the URL of the repository and a completely empty folder in our machine.

The URL of the repository is:

https://github.com/bernatagulloesbrina/TE-Scripting-in-Visual-Studio

The folder in your machine can be any you like, just make sure it’s completely empty or you’ll get an error.

After a few seconds the repository should be cloned in your machine.

Now activate the Solution explorer and double-click the solution

Now you’ll see that the solution has 2 projects: GeneralFunctions and TE Scripts. If you have read the previous posts I wrote on the topic you know where this is going.

Fixing the references

If we now open the «References» from the TE Scripts projects we’ll see something bad — there’s a couple references that are not found.

These are the Tabular Editor references. I could have added the files to the repository, but it’s better to refer to the files of your installation since you’ll update it and you are using it anyway, right?

Anyway, right click on references of the project TE Scripts –> Add Reference –> Browse

Enter this folder:

%APPDATA%\..\Local\TabularEditor

and select TOMWrapper14.dll. (f you don’t find the folder it means you have never executed TE2. It happended to me once. The folder and file is created when you first execute TE2.)

Now enter this folder

%programfiles(x86)%\Tabular Editor

and select TabularEditor.exe (even if you have TE3, we need to set the references to TE2 to avoid breaking the EULA of TE3)

Repeat to add both references for project GeneralFunctions.

However when we open TE Scripts.cs, after a while we see the dreaded red squiggly lines in some words. And that’s never good.

UPDATE (15-May-2023):  Turns out this is completely fine! We just need to right-click on the project and click «build». This will download the required packages and life will go on. Yet another shout out to Xavi Paterna for assistance on making the process even easier. (In a previous version of this article I detailed a workaround installing higher version of a package and then the old version of the package to solve this)

And bam! The error messages are gone and the red squiggly lines too!

I know it’s not as elegant as I hoped, but if it works, it works. And is not that bad.

Update: I think it’s dope now.

Now we should be good to go in terms of references! — no more red underlines and all keywords should have a different color

Setting up the macro-copier-macro in Tabular Editor

Wait what? Yes, as I was mentioning at the beginning, the process of copying the code to TabularEditor is a bit painful because each time you need to:

1. select all the code of the macro, which can be quite long
2. copy it
3. go to tabular editor
4. paste it
5. uncomment the external references
6. go back to visual studio
7. select all the code of the custom class (which can be super long)
8. paste it at the end
9. fix the closing curly bracket

Of course you don’t have to do all the steps all the time, but doing only some parts of it sometimes is more tedious and is always more prone to error. And we do not like either.

The script that is already included will make this a breeze. I’ve dreamed about writing this for a long long time (well, like every time I was copying code to Tabular Editor)

When I prepared all this with TE3, the story was very much straight forward. Copy some code, uncomment some lines, and bam, you are good to go. However, since I wanted to make it also TE2 compatible, that took some extra effort to find a way. The reason is that the script uses some references that do not come with TE2 (but come with TE3 — of course it’s Daniel Otykier who pointed this out), but not all is lost, we just need a couple of extra steps for this initial set up. After that we get the same experience.

The script as it is cannot be run from TE2, but we can put it inside a dll file, and then call the method of the class of that dll file. Creating a dll file sounds supper techie, but actually we’re just a couple clicks away from that. Dll files are also called libraries and guess what, the two projects of the solution we have are Class Library projects and that’s why when we build either project we get a dll file in a rather hidden subfolder. Then we can reference that dll from TE2 and call the methods of the class contained in the dll. Cool huh? Actually that was my first attempt at having a repository of code I could reuse — this time around we’ll just use it for this single macro to copy other macros. These other macros may or may not have a custom class, but that will be in-script, which makes distribution of the script much much easier. (I have discussed in-script classes in this article)

Anyway, let’s go step by step:

1. Right click con the TE Scripts project (not the TE Scripts.cs file) and select «Build»
   
2. Right click again on the TE Scripts project and select «Open Folder in File Explorer»
   
3. Double click on «Bin» folder, double click on «Debug»
4. Look for a file called TE Scripts.dll, Right click on it and select «Copy as path» (or Shift+Right Click if you don’t see the option)
   
5. Go back to Visual Studio and double click on TE Scripts.cs file. Look for «CopyMacroFromVSFileWithDll» method
   
6. Paste the path you just copied replacing <HERE FULL PATH TO TE Scripts.dll> removing the < > in the process too. (It will paste with double quotes so select them to before pasting)
   
7. Right click con the ‘TE Scripts.cs’ file and select «Copy Full Path»
   
8. Select the <HERE FULL PATH TO TE_Scripts.cs FILE> (including the <> signs) and replace it with the full path you just copied (this time it will come without the double quotes so don’t select them:
9. Now Copy the path of ‘GeneralFunctions.cs’ file and paste it on <HERE FULL PATH TO GeneralFunctions.cs FILE>
10. Save the changes! (seriously, is part of the process)
11. Select all the code of the method without the name and the opening and closing curly brackets
    
12. Go to Tabular Editor 2 and paste it in the C# tab
13. Uncomment only the lines starting with «#r» and «using»
    
14. Execute it with the «play button»! (you need to have a model loaded in Tabular Editor to execute scripts, if you do not have Power BI Desktop around just open a Model.bim file)
15. When prompted select «CopyMacroFromVSFileWithDll»
    
16. Now select all the code of the C# Script tab of Tabular Editor and push CTRL+V. You’ll see the code but much nicer, without the NOCOPY comments.
    
17. Store it as macro and select at least «Model» (it does not really matter, but we need to select something, I selected everything) and name it «Get Macro» (or something like that)
    
18. Now whenever you want you can right click on model (or any of the other elements selected on the previous step) –> Macros –> «Get Macro»
    
19. In the prompt you will get a list of all the non-public methods of the class, which are the C# scripts you will have
20. Select the C# script you want to copy, go to the C# Script Tab and paste it there, voilà! If they include «//using GeneralFunctions; » at the beginning the custom class will be copied underneath as an in-script class and the necessary references will be included on top of the script too (combining with any references that may exist there)

If you are lucky enough to have TE3, the process can be easier

1. Find the process «CopyMacroFromVSFile» in TE Scripts.cs
2. Uncomment the two rows starting with «//String macroFilePath» and «//String customClassFilePath». Red lines will show but it does not matter, it’s because this code is used by another method and this two variables are passed as arguments. You can comment them back at the end.
3. Replace the <HERE FULL PATH TO TE_Scripts.cs FILE> and <HERE FULL PATH TO GeneralFunctions.cs FILE> by the full paths of both files
   
4. Copy all the code of the method without the name and the opening and closing curly brackets
   
5. Paste it into a C# Script tab of Tabular Editor 3
6. Uncomment all the lines at the beginning starting with «#r» or «using»
7. Store as Macro, you can select all the different objects
   
8. you can then even set up a custom toolbar and no matter what you have selected you can always click on » Get Macro» (in this post I talk about how to add macros to a custom toolbar)

Wrap up

Ok, if you are not into c# scripting this post is completely prescindible, but if you are not I do think it is quite relevant to your coding exprience. Of course typing scripts for Tabular Editor is not as big as writting VBA macros for excel but still can solve some use cases that otherwise will be a no-go. If you don’t know what I mean, try doing what I did here without a script. But also scripts don’t need to be huge actually. The scripts that I create the most are those to create sum measures, selectedvalue measures, rows measures, and sometimes the filtered measures. One of the scripts I do use quite a lot too is the one on referential integrity measures.

Epilogue

There’s something that I did not discuss yet and is probably woth it. Once you clone this repository, you will not want to get any new update I do (because that would obliterate your changes) so set up your own fork

In case you are looking for the Github URL here it is again

https://github.com/bernatagulloesbrina/TE-Scripting-in-Visual-Studio

Thank you for reading such a long article. As usual, follow the conversation on Twitter and LinkedIn