Well, well, this is another article about C# Scripts. A topic that deserves much more attention than it’s getting. If you haven’t read my previous post, go do it right now because otherwise writing a c# script is a very frustrating experience. Not as much as building a Data Factory pipeline as a newbie, but almost.
If you have already programmed in other languages, many of this will be obvious, but anyway, is good to see how this applies to C# scripting for Tabular Editor. In this article we’ll talk about checking what is selected, checking what is in the model, interactions with the user and avoiding some of these interactions. Let’s get started!
Check what is selected
If you want to write a script that does something on what you selected, you must first check that indeed the user selected something that fits what you expect. Otherwise is then a good occasion to let the user know what is supposed to do to execute the script and stop de execution. Once you store the script as a Macro this problem normally is smaller since you already right click on the thing so it is selected. But still, it never hurts to check.
Everything that you have selected in the TOM Explorer is contained in the object «Selected». So if you plan to iterate on each selected measure and say modify the formatstring, you might as well start your script with
if(Selected.Measures.Count == 0)
{
Error("You have to select at least one measure!");
return;
};
Here notice a few things:
- the structure of the if statement: if(condition){ code }; It’s different from DAX, it’s different from M, it’s even different from VBA. Deal with it.
- C# is an object oriented language, that means that with «.» you can get to everything you want to know or do with an object (or almost).
- «Selected» is everything you have selected.
- «Selected.Measures» is a collection of all the measures that you have selected (excluding other things selected if any)
- «Selected.Measures.Count» is the number of selected measures.
- To compare two values we need to use == . A single = is for value assignment only.
- Error is a function to make a pop-up appear with an error mark in it along with any message you want. If you prefer a softer line with your user you can also use Info(«your message»).
- At the end of each statement we need to use a semi-column «;»
- «return» finishes the execution of the script.
I know it’s not a very orderly way to learn c# but at least you see how are things working in this very common use case.
If you wanted to, you could also write the following
if(Selected.Measures.Count != 0)
{
//all the code
}else
{
Error("You have to select at least one measure!");
return;
};
Doing it like this works just as well, but I don’t like it for 2 reasons:
- it makes all your code go one tab to the right, and I think it’s good to use tabs only when they are really necessary.
- When you get to the else clause, if your code is long you might not even remember what is this about, so it’s hard to read.
So it’s good to check everything before starting to do actual stuff.
Check the model
If your script has to affect all the model and not just the selected part you want to test that the model indeed has what you expect to find. If you want to iterate for each relationship, it might be a good idea to check that the model has indeed relationships before starting.
if(Model.Relationships.Count == 0) {
Error("No relationships found!");
return;
};
Complex checks
So far so good, but sometimes (even most of the times) we want to check more complex stuff. For instance, is there any column in a table that has a the terrible formatstring «mm/dd/yyyy» ? In order to do that we’ll use something called LINQ that uses lambda expressions to query your object. It sounds weird, but once you see it, it’s quite awesome. So, given a variable table (that already points to some table in the model), the following expression returns the columns with the formatstring «mm/dd/yyyy» :
table.Columns.Where(x =﹥ x.FormatString == "mm/dd/yyyy");
When we use .Where(condition) after a collection we are filtering the collection by a condition. To specify the condition a lambda expression is used. At first I was afraid of the «lambda expression» thing, until I understood it, as usual. Like in Power Query, first you define the variable that you’ll use, and then you write the expression using that variable. Here the important bit is that the variable will represent each object of the collection that comes right before the «Where». So in this case x will be a column and you can access all the properties of the column while building your expression. So if we want to check that there are some columns with that format, with what we already know we might be tempted to do something like:
But this, as we can see, will not work. There are a couple problems: One is from the type of the different objects involved, and another one is that if no object satisfies the condition it will also cause an error if you put something behind like .Count or .Delete() for instance. Luckily, LINQ has another method that will just return true if any of the objects fulfills the condition. And the method is, er, Any
If you want to make sure that no element of the collection fulfills certain condition, there is no «None» method in LINQ, but it’s just the oposite of Any, so you can just negate the whole thing, but C# likes to do it’s own thing, so instead of using «no» it uses «!». Here we make sure that there is no measure called «Sales» in the model.
if(!Model.AllMeasures.Any(m =﹥m.Name == "Sales"))
{
};
You can go mad on this thing. Here, for instance, this expression returns all fact tables of the model defined by «table that has at least one relationship, and all relationships are one-to-many and the table is always on the many side.
Model.Tables.Where(
x => Model.Relationships.Where(r =﹥ r.ToTable == x)
.All(r =﹥ r.ToCardinality == RelationshipEndCardinality.Many) ＆＆
Model.Relationships.Where(r =﹥ r.FromTable == x)
.All(r =﹥ r.FromCardinality == RelationshipEndCardinality.Many) ＆＆
Model.Relationships.Any(r =﹥ r.FromTable == x || r.ToTable == x)
);
Here you see that the Where condition is formed by 3 conditions that have to be met. The logical and in C# parlance is && and the or is ||. Is that a DAX reference??. Here you can see also that in C# the way to check certain attributes is to compare them with an object that contains all the different values. Without intellisense and some code assistance most of that is just too hard to guess. But with the correct coding environment that was described in the previous article, this becomes almost an enjoyable exercise.
Interaction with the user
In any script that wants to become a macro (and all scripts should aspire to become one) you need to let the user change the names of whatever is about to be generated, or choose a table, or measure or something or let him or her choose if certain action should be performed as well or not. The good news is that we can use very useful functions out of the box:
SelectTable: This function will return a table if the user selects and clicks ok (or null otherwise) so you will use it like:
Table myTable = SelectTable(label:"Choose a table to store the measure");
This will be the case 99% of the time. If you want to make the workflow smoother and you can have a pretty good guess on the table that the user will want to use, you can pass a table as «preselect» argument
Table myTable = SelectTable(preselect: guessTable, label:"Choose a table to store the measure");
For instance, in the latest iteration of the Time Intelligence Calc Group Creation script, I try to guess the date table. From those that fulfill my criteria I choose the first. Since I’m not sure if I’ll find any I have to check first with Any (later well show a better method I thought while writing this blog post)
Table dateTableCandidate = null;
if(Model.Tables.Any
(x =﹥ x.GetAnnotation("@AgulloBernat") == "Time Intel Date Table"
|| x.Name == "Date"
|| x.Name == "Calendar")){
dateTableCandidate = Model.Tables.Where
(x =﹥x.GetAnnotation("@AgulloBernat") == "Time Intel Date Table"
|| x.Name == "Date"
|| x.Name == "Calendar").First();
};
Table dateTable =
SelectTable(
label: "Select your date table",
preselect:dateTableCandidate);
Here I check if there are any tables that fulfill the criteria and if any found then it chooses the first one as a candidate that will be passed as preselect argument. If your guess is right you’ll save a click to the end-user! A relevant bit of the code snippet is that you have to declare the variable before the if statement. If you declare a variable inside an if statement you can not use it outside of it, which is what we want to do. It makes sense, I guess.
SelectMeasure: Here most of the time you will specify just the label parameter (or not even that one) letting the user select a measure and that’s it. But like tables you might want to restrict the list using some LINQ expression. The good thing about measures is that you can use Model.AllMeasures.The following expression let’s you select any measure of your model that is a Date
Measure measure =
SelectMeasure(Measures:Model.AllMeasures
.Where(m =﹥ m.DataType == DataType.DateTime))
SelectColumn: This one by default it expects that you provide a table, and from there it will let you choose which column of the table you want. I think that’s the best approach, easy to understand all good. But, if you want, you can provide a list of columns belonging to different tables. After all the columns will appear fully qualified (i.e. with the table name and all) to the user to choose. In any case you need to specify either a table or the column collection.
Asking for a name
Letting the user choose the name of anything that is going to be created on the model I would say is good practice. And it’s even a better practice to suggest a name for all those lazy efficient users. We can achieve this in C# scripts too, but we need to load a library (!)
Here the things get a little trickier, because in Visual Studio is enough that we add the «using» statement, but in Visual studio we may need to add a reference to the dll. Well if it’s not a basic dll, in Visual Studio we also need to add the reference to the dll, right? so it’s pretty much the same thing. Anyway, to ask the user for a string input we’ll use the «Interaction.InputBox» method that is part of Micosoft VisualBasic library. How did I konw that? I didn’t. Daniel Otykier told me (like 95% of the content of this blog post).
In VisualStudio we just add
using Microsoft.VisualBasic;
at the top. In Tabular Editor we add a reference to the library, started with #r. Here we see how the code might look in Tabular Editor.
#r "Microsoft.VisualBasic"
using Microsoft.VisualBasic;
String newMeasureName =
Interaction.InputBox(
Prompt: "Enter name for the new measure:",
Title: "Measure name",
DefaultResponse: "Kpi"
);
if(newMeasureName == "") {
Error("No name provided");
return;
}
This is all good, but naming thing is harder than it looks if you want to do it well. What if the user picks a name that cannot be used? You might want to add extra logic to check for these things, parametrized to whatever is naming. It it’s a measure there should not be any other measure with the same name (or even a column if it’s in the same table!), for tables you might not use the same name as an existing table, etc. you get the idea. Of course building all this for *each* name you need is way too much, so this too we’ll see how can it be encapsulated for reuse in a future post.
An important thing to keep in mind is that since the user might cancel your script by not providing a name, you might want to ask for the names early on, but not too much! What if you ask for the name of the new measure just to realize that the script cannot be run because there’s no date table? this will cause frustration to the user that carefully provided all the custom names that was asked for. So check first for the selection/model requirements, then ask for the custom names, then start doing actual things.
Asking Yes or No
Some actions of your script might be optional. How can we give the user the option to say Yes or No ? This again lives in yet another library. The method is MessageBox.Show and as you’ll see it as a LOT of overloads:
That means that this method can be called in many different ways, feel free to explore it. We’ll just go straight to the Yes No mode. First things first, this method belongs to Windows forms, so we’ll need to add
using System.Windows.Forms;
at the very top. Luckily for us this one does not require to add the #r line in Tabular Editor. The usage we can see it in the beginning of the script of this blog post
DialogResult dialogResult = MessageBox.Show("Generate Field Parameter?", "Field Parameter", MessageBoxButtons.YesNo);
generateFieldParameter = (dialogResult == DialogResult.Yes);
As you see you might want to use code similar to this one as the result is not yet a boolean, it’s a DialogResult value! So in order to see if the user said yes we have to compare it with «DialogResult.Yes». You may not like it, but as they say, «it is what it is».
Avoiding interaction with the user if possible!
If we are making a script related with time intelligence we might want to find the date table of the model (and the date column!). But maybe there’s no need to ask the user anything. If there’s only one table defined as date table we can just use that one and avoid bugging the user for something that we can check ourselves.
Like the macro recorder for Excel, the macro recorder for TE3 will not write the whole script we need, but it will provide interesting bits when we have no idea where to look for. I recorded a macro and then configured a date table as, well, date table. I got this two lines of code
Model.Tables["Date"].DataCategory = "Time"; Model.Tables["Date"].Columns["Date"].IsKey = true;
To detect the date table we could go with a code like this
Table dateTable = null;
Table dateTableGuess = null;
IEnumerable﹤Table﹥ dateTableCandidates = null;
int dateTableCount = 0;
if(Model.Tables.Any(t =﹥ t.DataCategory == "Time"))
{
dateTableCandidates = Model.Tables.Where(t =﹥ t.DataCategory == "Time");
dateTableGuess = dateTableCandidates.First();
dateTableCount = dateTableCandidates.Count();
}
if(dateTableCount == 0)
{
//no date table defined
Error("No date table found");
return;
}else if (dateTableCount == 1)
{
//no need to ask anything
dateTable = dateTableGuess;
}
else
{
//ask which of the date tables is to be used
dateTable =
SelectTable(
Tables: dateTableCandidates,
preselect: dateTableGuess,
label:"Choose the date table to use"
);
}
This is already pretty neat, but then we need to write some more code to get to the date column as well. We can just check for columns that are key in tables of the DataCategory «time». We follow the same strategy but by selecting the column (or finding just one) then we have both the column and the table!
Column dateColumn = null;
Column dateColumnGuess = null;
IEnumerable dateColumnCandidates = null;
int dateColumnCount = 0;
if(Model.AllColumns
.Any(c =﹥c.IsKey ＆＆ c.Table.DataCategory == "Time"))
{
dateColumnCandidates =
Model.AllColumns
.Where(c =﹥ c.IsKey ＆＆ c.Table.DataCategory == "Time");
dateColumnGuess = dateColumnCandidates.First();
dateColumnCount = dateColumnCandidates.Count();
}
if (dateColumnCount == 0)
{
Error("No date columns in date tables found");
return;
}else if(dateColumnCount == 1)
{
dateColumn = dateColumnGuess;
}
else
{
dateColumn = SelectColumn(dateColumnCandidates);
if(dateColumn == null)
{
Error("You canceled.");
return;
}
}
dateTable = dateColumn.Table; //if you even need this one
In this snippet another bit worth mentioning is the if(dateColumn == null). This will happen if the user cancels the dialog box. Always think of such cases to avoid hard-errors (I think they are called uncontrolled errors), because these freak out people and decrease their confidence in the code. Much better to check if the user closed the dialog box the wrong way and then be you who throws a nice error message with some guidance on what went on. It’s more work, but in later articles we’ll see how to encapsulate this so that we only have to type it once and reuse forever.
Annotations are your friend
Something that I have found very useful in avoiding user interactions is adding model annotations whenever you ask something to the user o you create some new thing in the model. That way when the script is run for a second time you can check if those things are already in place and avoid asking about them again. Most common is when you create a calculation item for each measure or column selected. The first time you’ll have to create the calculation group, including asking for the name etc, but the second time you can just go straight to the calculation group and just add the new calculation item!
Annotations can be added in almost anything on your model, but you need to enable unsupported actions in Power BI. So far I have faced no issue, but then again, no warranty and save your work!
The best way to go about this is first declare to string variables with some unique-ish values. This one is from the dynamic measure calculation group script (evolved from the one from Johnny Winter)
string dynamicMeasureCgTag = "@GreyskullPBI"; string dynamicMeasureCgValue = "Dynamic Measure Calculation Group";
An annotation is a pair of two strings, one is the tag the other the value. At least is how I call them. So when I create the calculation group (or get the user to select it) I add the annotation to find it even faster the next time the script is run on the same model. To add the annotation is fairly simple:
cgTable.SetAnnotation(dynamicMeasureCgTag, dynamicMeasureCgValue);
That allows me to search for it directly as it is highly unlikely that another table would have such an annotation.
var dynamicCGs = Model.Tables
.Where(x =﹥x.GetAnnotation(dynamicMeasureCgTag) == dynamicMeasureCgValue);
CalculationGroupTable cgTable = null as CalculationGroupTable;
// CalculationGroup cg = null as CalculationGroup;
if(dynamicCGs.Count() == 1)
{
//reuse the calc group
cgTable = dynamicCGs.First() as CalculationGroupTable;
}
else if (dynamicCGs.Count() ﹥ 1)
{
//create the calc group
}
else
{
//make them choose the calc group -- should not happen!
cgTable =
SelectTable(
dynamicCGs,
label: "Select your Dynamic Measure Calculation Group For Arbitrary 2-row Header"
) as CalculationGroupTable;
}
As you can see I could keep writing about this for hours, but I think this is a good point to stop. Hopefully this will provide some inspiration to write your first C# script.
Follow the conversation in 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