If you are new to using C# and the Tabular Object Model (TOM), please check out the previous blog post (https://dataonwheels.wordpress.com/2021/10/15/power-bi-meets-programmability-tom-xmla-and-c/) for both an introduction to the topic and detailed instructions on getting the demo stood up.
For the TOM and XMLA experts, imagine this. Your customer wants to dynamically rename columns without using the Power BI Desktop and would prefer all existing report visuals not get broken by the new name. Impossible? Not with TOM, XMLA, and translations within Power BI.
If you’ve ever tried to change a column name in a Power BI source, you’ve likely run into this error on any visuals that contained the renamed column. And when you hit that “See Details”, it will tell you the column that you simply renamed is no longer available for your visual.
So how do we get around that? Translations. Translations are typically used to translate report entities to other languages that will change depending on what language the end user has set on their browser. However, we can hijack this functionality to rename columns without having to impact the data model. It is a bit confusing on why this works, but imagine this: you build a Lego pyramid, but learn that one of the blocks needs to be changed from blue to green. Couple of options, you can take apart the entire pyramid (this would be akin to reopening the PBIX in Power BI Desktop and changing all of your visuals) OR you can take a green marker and color that blue brick green (adding a translation from blue to green).
If you don’t need to put this code into C#, the Tabular Editor is an excellent tool for adding translations to your data model (https://tabulareditor.com/creating-multilingual-power-bi-datasets/). However if you would like to programmatically update column names using C#, feel free to use the script below in your solution.
At a high level, here’s the hierarchy of entities used:
Workspace – Dataset – Data Model – Cultures – Object Translations
Workspace – Dataset – Data Model – Table – Column – Translated Properties
Note: There can only be one translated property per culture.
To add translations, we first need to set which culture this translation belongs in. For this example, we will use “en-US” because that is what default browser we want these names applied to. The code snippet below will list out all the cultures (aka website language codes) that are configured in this data model and list out all the translated objects (data columns in this case) that already exist.
After setting the culture/language, narrow down the column that this translation will be applied to and create a variable for the translation object. The translation object consists of two parts, the metadata object (column in this example) and the property of that metadata that we want to translate (caption in this example which is essentially display name).
Once we have these elements, we can check to see if this column already has a translation for this culture. If it does, this script will remove the old translation to allow for overwriting. If it does not, it will add the new translation to the culture within the data model.
And that’s it!
Here’s what it looks like in the service. Don’t forget to refresh your report page if you have it open for the new name to appear. There’s no need to refresh the dataset.
Full C# code:
using System;
using Microsoft.AnalysisServices.Tabular;
namespace PowerBI_TOM_Testing
{
class Program
{
static void Main()
{
// create the connect string - powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE_NAME
string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOURWORKSPACE";
string connectString = $"DataSource={workspaceConnection};";
// connect to the Power BI workspace referenced in connect string
Server server = new Server();
server.Connect(connectString);
// enumerate through datasets in workspace to display their names
foreach (Database database in server.Databases)
{
Console.WriteLine($"ID : {database.ID}, Name : {database.Name}, CompatibilityLevel: database.CompatibilityLevel}, Last Updated : {database.LastSchemaUpdate}");
}
// enumerate through tables in one database (use the database ID from previous step)
Model model = server.Databases["bb44a298-f82c-4ec3-a510-e9c1a9a28af2"].Model;
//if you don't specify a database, it will only grab models from the first database in the list
foreach (Table table in model.Tables)
{
Console.WriteLine($"Table : {table.Name} IsHidden? : {table.IsHidden}");
}
// Specify a single table in the dataset
Table table_product = model.Tables["Product"];
// List out the columns in the product table
foreach (Column column in table_product.Columns)
{
Console.WriteLine($"Columns: {column.Name}");
}
//Translations can be used to rename existing columns without rebuilding the model. This also updates any visuals that use that column.
// List of translations on the model
foreach (Culture culture in model.Cultures)
{
Console.WriteLine($"Existing Culture: {culture.Name}");
}
// Let's get a list of the existing translations within the en_US culture
Culture enUsCulture = model.Cultures.Find("en-US");
foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations)
{
Console.WriteLine($"Translated Object: {objectTranslation.Value}");
}
// Narrow down what column within this culture/language you would like to add the translation to
MetadataObject dataColumn = table_product.Columns.Find("Description"); //this needs to always be the original column name within the data model.
ObjectTranslation proposedTranslation = enUsCulture.ObjectTranslations[dataColumn, TranslatedProperty.Caption];
// Only one translation per entity per culture.
if (proposedTranslation != null)
{
Console.WriteLine($"Translation Exists for this Culture & Column combo");
enUsCulture.ObjectTranslations.Remove(proposedTranslation); //need to remove the existing translation to overwrite it
ObjectTranslation overwriteTranslation = new ObjectTranslation()
{
Object = dataColumn,
Property = TranslatedProperty.Caption,
Value = "Blue"
};
enUsCulture.ObjectTranslations.Add(overwriteTranslation);
}
else
{
ObjectTranslation newTranslation = new ObjectTranslation()
{
Object = dataColumn,
Property = TranslatedProperty.Caption,
Value = "Blue"
};
enUsCulture.ObjectTranslations.Add(newTranslation);
}
// List out the translations to see what they are now that we have run the script
foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations)
{
Console.WriteLine($"Final Translated Object: {objectTranslation.Value}");
}
model.SaveChanges(); //make sure this is the last line!
}
}
}
Additional Resources:
– https://www.kasperonbi.com/setting-up-translations-for-power-bi-premium/
– https://tabulareditor.com/creating-multilingual-power-bi-datasets/
– https://www.sqlbi.com/tools/ssas-tabular-translator/
