Powerbi 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

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:

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

// 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

Environment Variables (for XMLA)

AZURE_CLIENT_ID=<app-id>
AZURE_TENANT_ID=<tenant-id>
AZURE_CLIENT_SECRET=<secret>

Execution Methods

1. Tabular Editor CLI

# Inline script
TabularEditor.exe "WorkspaceName/ModelName" -S "Info(Model.Database.Name);"

# Script file
TabularEditor.exe "WorkspaceName/ModelName" -S "script.csx"

2. Connection Types

Core Objects

The Model Object

Access any object in the loaded Tabular Model:

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):

// 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().

// 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.

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:

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:

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:

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:

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 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.

Common Workflows

See references/common-workflows.md for complete workflow scripts: bulk format measures, create time intelligence measures, configure RLS, audit hidden objects. Also check examples/ for 180 working .csx scripts before writing from scratch.

Debugging & Troubleshooting

Script Doesn't Complete

Add Info() checkpoints to find where script fails:

Info("Step 1: Starting");
var table = Model.Tables["Sales"];
Info("Step 2: Got table");
var measure = table.AddMeasure("Test", "1");
Info("Step 3: Added measure");  // If this doesn't appear, AddMeasure failed

Object Not Found

Check existence before accessing:

if(Model.Tables.Contains("Sales")) {
    var table = Model.Tables["Sales"];
    // ...
} else {
    Error("Table 'Sales' not found");
}

// Or use FirstOrDefault
var table = Model.Tables.FirstOrDefault(t => t.Name == "Sales");
if(table == null) {
    Error("Table not found");
}

Changes Not Appearing

• XMLA operations may take 2-5 seconds to sync
• Refresh Power BI Desktop connection after changes
• Check for silent errors (add Info() after each operation)

TE2/TE3 Compatibility

Use preprocessor directives for version-specific code:

#if TE3
    // TE3-specific code (version 3.10.0+)
    Info("Running in Tabular Editor 3");
#else
    // TE2 fallback
    Info("Running in Tabular Editor 2");
#endif

Check version at runtime:

var majorVersion = Selected.GetType().Assembly.GetName().Version.Major;
if(majorVersion >= 3) {
    // TE3 code
}

Best Practices

1. Add Info() statements - Track script execution and catch errors early
2. Check object existence - Use .Contains() or .Any() before accessing
3. Use bulk operations - Single script with loops is faster than multiple scripts
4. Test on dev models - Never test new scripts on production
5. Use @"..." for DAX - Multi-line strings for DAX expressions
6. Format with FormatDax() - After creating measures/columns
7. Set DisplayFolder with / - Forward slashes auto-convert to backslashes
8. Hide the wait spinner - ScriptHelper.WaitFormVisible = false; for UI dialogs

Additional Resources

Reference Files

• object-types/ - Detailed API docs per object type
• examples/ - 180 working .csx scripts across 20 categories; always check here before writing from scratch

Fetching Docs

To retrieve current TOM API reference docs, use microsoft_docs_search + microsoft_docs_fetch (MCP) if available, otherwise mslearn search + mslearn fetch (CLI). Search based on the user's request and run multiple searches as needed to ensure sufficient context before proceeding.

External References

• Tabular Editor Advanced Scripting
• TOM API Reference
• C# Scripts and Macros
• Script Library