As a data enthusiast, I’m always on the lookout for ways to streamline my workflow and make my life easier. In this post, I’ll be sharing two Tabular Editor scripts that have saved me countless hours of manual formatting in my Power BI projects.
There are two versions of this software. To use the scripts below you only need version two. This version is free and open source: https://tabulareditor.github.io/TabularEditor/
If you have the money for the license you can support the author (Daniel Otykier) by buying the license for version 3: https://tabulareditor.com
If you prefer a nice introductory video to the tool, here’s a presentation from the author: Tabular Editor empowering the Power BI - by Daniel Otykier @ RADACAD
Script 1: Looping through Date Dimensions and Sorting Columns
Let’s face it - when working with large datasets, manually sorting columns can be a tedious task. This script takes care of that for you by looping through all the date dimensions in your model and sorting their corresponding columns.
Here’s the script:
foreach(var table in Model.Tables.Where(t => t.Name.Contains("Date")))
{
foreach(var column in table.Columns)
{
if(column.Name.Contains("YearMonthName"))
{
column.SortByColumn = table.Columns[column.Name.Replace("Name","Code")];
}
if(column.Name.EndsWith("YearWeek"))
{
column.SortByColumn = table.Columns[column.Name + "Code"];
}
}
}
To use this script, simply open your Tabular Editor, navigate to the script editor, and paste in the code. Then, click Run and watch as your date dimensions are sorted with ease!
Script 2: Adding Swiss Numbering Format (with Decimal Places)
When working with financial data, it’s often necessary to display numbers in a specific format - like Swiss numbering format (with or without decimal places). This script takes care of that for you by adding the correct formatting expression to each selected measure in your model. For this to work I’m using the Dynamic Formatting preview feature.
Here’s the script:
foreach(var m in Selected.Measures)
{
m.FormatString = "";
m.FormatStringExpression = @"SWITCH (
TRUE(),
ABS ( SELECTEDMEASURE() ) < 1000, ""0.00"",
ABS ( SELECTEDMEASURE() ) >= 1000 && ABS ( SELECTEDMEASURE() ) < 1000000, ""#\'###.00"",
ABS ( SELECTEDMEASURE() ) >= 1000000 && ABS ( SELECTEDMEASURE() ) < 1000000000, ""#\'###\'###.00"",
ABS ( SELECTEDMEASURE() ) >= 1000000000, ""#\'###\'###\'###.00""
)";
}
To use this script, simply open your Tabular Editor, navigate to the script editor, and paste in the code. Then, click Run and watch as your measures are formatted with Swiss numbering format (with decimal places).
Conclusion
In this post, I’ve shared two Tabular Editor scripts that have simplified my Power BI modeling experience. With these scripts, you can automate tedious tasks like sorting columns and formatting numbers - freeing up more time for data analysis and visualization.
Whether you’re a seasoned Power BI user or just starting out, I hope these scripts are helpful in streamlining your workflow. Happy modeling!