c-sharp-scripting

# C# Scripting for Tabular Editor Expert guidance for writing and executing C# scripts to manipulate Power BI semantic model metadata using Tabular Editor 2/3 CLI or the Tabular Editor IDE. ## When to Use This Skill Activate automatically when tasks involve: - Writing C# scripts for Tabular Editor - Bulk operations on model objects (measures, columns, tables) - Creating or modifying calculation groups - Managing model security (roles, RLS, OLS) - Formatting DAX expressions - Automating repetitive model changes - Querying model metadata via TOM API - Building interactive scripts with user input dialogs ## Critical - Every statement must end with `;` (semicolon required by C#) - Use double quotes `"` for strings and escape with `\` when needed - Use forward slashes `/` in DisplayFolder paths (auto-converted to `\`) - Always add `Info()` statements for debugging - script stops at error point - Test scripts on non-production models first - Changes are undoable with Ctrl+Z in the Tabular Editor UI ## C# Version Support | Environment | C# Version | Notes | |-------------|------------|-------| | **Tabular Editor 2** | Default compiler | Older C# syntax | | **Tabular Editor 3** | Roslyn | Supports up to C# 12 with VS2022 | | **TE2 with Roslyn** | Configurable | Set in File > Preferences > General | To use newer C# features in TE2, configure Roslyn compiler path in preferences. ## Default Imports and Assemblies ### Auto-Imported Namespaces Scripts automatically have these `using` statements applied: ```csharp using System; using System.Linq; using System.Collections.Generic; using Newtonsoft.Json; using TabularEditor.TOMWrapper; using TabularEditor.TOMWrapper.Utils; using TabularEditor.UI; ``` ### Pre-Loaded Assemblies These .NET assemblies are loaded by default: - `System.Dll` - `System.Core.Dll` - `System.Data.Dll` - `System.Windows.Forms.Dll` (for UI dialogs) - `Microsoft.Csharp.Dll` - `Newtonsoft.Json.Dll` - `TomWrapper.Dll` - `TabularEditor.Exe` - `Microsoft.AnalysisServices.Tabular.Dll` ### Adding External Assemblies ```csharp // Assembly references must be at the very top of the file: #r "System.IO.Compression" #r "System.Drawing" // Using statements come after assembly references: using System.IO.Compression; using System.Drawing; ``` ## Prerequisites ### For Tabular Editor CLI | Requirement | Description | |-------------|-------------| | **Tabular Editor 2 CLI** | Download from [GitHub releases](https://github.com/TabularEditor/TabularEditor/releases) | | **XMLA Read/Write** | Enabled on Fabric capacity or Power BI Premium | | **Azure Service Principal** | For XMLA connections (see authentication.md) | ### Environment Variables (for XMLA) ``` AZURE_CLIENT_ID=<app-id> AZURE_TENANT_ID=<tenant-id> AZURE_CLIENT_SECRET=<secret> ``` ## Execution Methods ### 1. Tabular Editor CLI ```bash # Inline script TabularEditor.exe "WorkspaceName/ModelName" -S "Info(Model.Database.Name);" # Script file TabularEditor.exe "WorkspaceName/ModelName" -S "script.csx" ``` ### 2. Connection Types | Type | Format | Example | |------|--------|---------| | **XMLA** | `workspace/model` | `"Sales WS/Sales Model"` | | **Local BIM** | `path/to/model.bim` | `"./model.bim"` | | **Local TMDL** | `path/to/definition/` | `"./MyModel.SemanticModel/definition/"` | | **PBI Desktop** | `localhost:PORT` | `"localhost:52123"` | ## Core Objects ### The `Model` Object Access any object in the loaded Tabular Model: ```csharp Model // Root model object Model.Tables // All tables Model.Tables["Sales"] // Specific table Model.AllMeasures // All measures across all tables Model.AllColumns // All columns across all tables Model.Relationships // All relationships Model.Roles // All security roles Model.CalculationGroups // All calculation groups Model.Perspectives // All perspectives Model.Cultures // All translations/cultures Model.Expressions // All M expressions (shared queries) Model.DataSources // All data sources ``` ### The `Selected` Object Access objects currently selected in the TOM Explorer (IDE only): ```csharp // Plural form - collections (safe even when empty) Selected.Tables // Selected tables Selected.Measures // Selected measures Selected.Columns // Selected columns Selected.Hierarchies // Selected hierarchies // Singular form - single object (error if not exactly one selected) Selected.Table // The single selected table Selected.Measure // The single selected measure Selected.Column // The single selected column // Set properties on multiple objects at once Selected.Measures.DisplayFolder = "Test"; Selected.Columns.IsHidden = true; // Bulk rename with pattern Selected.Measures.Rename("Amount", "Value"); ``` When a Display Folder is selected, all child items are included in the selection. ## LINQ Fundamentals LINQ is essential for filtering and transforming TOM collections. See **`references/linq-reference.md`** for the full method table, lambda syntax, and examples. Key methods: `Where()`, `First()`, `FirstOrDefault()`, `Any()`, `All()`, `Count()`, `Select()`, `OrderBy()`, `ForEach()`, `ToList()`. ```csharp // Common pattern: filter, chain, act Model.AllMeasures .Where(m => m.Name.Contains("Revenue")) .Where(m => string.IsNullOrEmpty(m.FormatString)) .ForEach(m => m.FormatString = "$#,0"); ``` ## Helper Methods See **`references/helper-methods.md`** for the complete reference including Output() variations, file operations, property export/import, interactive selection dialogs, DAX formatting/execution, and macro invocation. | Method | Purpose | |--------|---------| | `Info(message)` | Display info popup (CLI: writes to console) | | `Warning(message)` | Display warning popup | | `Error(message)` | Display error popup and stop script | | `Output(object)` | Display detailed object inspector dialog | | `SaveFile(path, content)` / `ReadFile(path)` | File I/O | | `ExportProperties()` / `ImportProperties()` | TSV export/import | | `SelectMeasure()` / `SelectTable()` | Interactive selection (IDE only) | | `FormatDax()` / `CallDaxFormatter()` | DAX formatting | | `EvaluateDax()` / `ExecuteDax()` | DAX execution (connected) | ## WinForms UI Patterns For interactive dialogs (input forms, dropdowns, multi-select), see **`references/winforms-dialogs.md`**. Key setup: `#r "System.Drawing"`, `ScriptHelper.WaitFormVisible = false;` ## Quick Reference ### Core Patterns **Add a Measure:** ```csharp var m = Model.Tables["Sales"].AddMeasure("Total Revenue", "SUM(Sales[Amount])"); m.FormatString = "$#,0"; m.DisplayFolder = "Key Metrics"; m.Description = "Total sales revenue"; Info("Added: " + m.Name); ``` **Iterate Tables/Columns:** ```csharp foreach(var t in Model.Tables) { foreach(var c in t.Columns.Where(c => c.Name.EndsWith("Key"))) { c.IsHidden = true; } } Info("Hidden key columns"); ``` **Conditional Operations:** ```csharp foreach(var m in Model.AllMeasures) { if(m.Name.Contains("Revenue")) m.FormatString = "$#,0"; if(m.Name.Contains("Rate")) m.FormatString = "0.00%"; } ``` **Create Calculation Group:** ```csharp var cg = Model.AddCalculationGroup("Time Intelligence"); cg.Precedence = 10; var ytd = cg.AddCalculationItem("YTD", "CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))"); var prior = cg.AddCalculationItem("Prior Year"); prior.Expression = @" CALCULATE( SELECTEDMEASURE(), DATEADD('Date'[Date], -1, YEAR) ) "; Info("Created calculation group"); ``` ### TOM API Quick Reference | Object | Access | Common Properties | |--------|--------|-------------------| | **Model** | `Model` | `.Tables`, `.AllMeasures`, `.Relationships` | | **Table** | `Model.Tables["Name"]` | `.Measures`, `.Columns`, `.Partitions`, `.IsHidden` | | **Measure** | `Table.Measures["Name"]` | `.Expression`, `.FormatString`, `.DisplayFolder`, `.Description` | | **Column** | `Table.Columns["Name"]` | `.DataType`, `.FormatString`, `.IsHidden`, `.SummarizeBy` | | **Relationship** | `Model.Relationships` | `.FromTable`, `.ToTable`, `.IsActive`, `.CrossFilteringBehavior` | | **Role** | `Model.Roles["Name"]` | `.Members`, `.TablePermissions` | | **Hierarchy** | `Table.Hierarchies` | `.Levels`, `.DisplayFolder`, `.IsHidden` | | **Partition** | `Table.Partitions` | `.Expression`, `.SourceType`, `.DataSource` | | **Perspective** | `Model.Perspectives` | Objects have `.InPerspective["Name"]` | | **Culture** | `Model.Cultures` | Objects have `.TranslatedNames["culture"]` | ## Object Type Reference Detailed documentation for each object type in `object-types/`: - `tables.md` - Table CRUD, properties, partitions - `columns.md` - Column types, properties, sorting - `measures.md` - Measure creation, formatting, organization - `relationships.md` - Relationship management - `calculation-groups.md` - Calculation groups and items - `roles.md` - Roles, RLS, OLS configuration - `hierarchies.md` - Hierarchy and level management - `partitions.md` - Partition types and configuration - `perspectives.md` - Perspective membership - `translations.md` - Culture and translation management - `annotations.md` - Custom metadata annotations ## Example Scripts 180 working `.csx` scripts organized by category in `examples/`. Before writing a script from scratch, check if a relevant example already exists -- read the example, adapt it to the task, and modify as needed. | Category | Scripts | Description | |----------|---------|-------------| | `bulk-operations/` | 8 | Model initialization, batch updates, clean names, sync folders, validate | | `calculation-groups/` | 2 | Time intelligence, currency conversion | | `columns/` | 20 | Data types, hiding, sorting, encoding hints, cardinality, properties | | `cultures/` | 4 | Add/delete cultures, list, modify translations | | `display-folders/` | 8 | Organize by type, clear, rename, add/remove folders | | `evaluate-dax/` | 5 | Execute DAX, scalar queries, table queries, column sizes, optimize | | `format-dax/` | 8 | Format measures, calculated columns/tables, KPIs, detail rows | | `format-strings/` | 6 | Apply by name/pattern, custom formats, dynamic format strings | | `functions/` | 4 | Add/delete/list/modify shared M functions | | `hierarchies/` | 4 | Add/delete/list/modify hierarchies | | `kpis/` | 4 | Add/delete/list/modify KPIs | | `measures/` | 18 | Full CRUD, time intelligence, bulk create, move, hide/unhide | | `model/` | 10 | Properties, compatibility level, dependencies, refresh, export stats | | `partitions/` | 6 | Refresh, find-replace M, incremental refresh, hybrid tables |