Localizing the Data Model - Part 1

[Greg_Deckler]

Introduction

So a user in my user group recently asked me about localizing a Power BI data model including table names, column names, measure names as well as titles for visualization, etc. I figured I'd break this subject up into chunks so there will at least be two parts to this blog article, maybe three or more. Let's start with the "easy" stuff, table, column and measure names.

Setup

OK, to pull this off, you are going to need a few things. First, if you haven't installed Tabular Editor, you need to do that, either version 2 or version 3. Power BI's data model does support localization settings but that functionality isn't exposed in Power BI Desktop. Second, let's build a terribly simple data model to work with. Open Power BI Desktop and create the following table:

Dates = 
    ADDCOLUMNS(
        CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),
        "Month",FORMAT([Date],"mmmm"),
        "Year", YEAR([Date]),
        "Weekday",FORMAT([Date],"dddd")
    )

For good measure, let's create a measure:

Number of Days = COUNTROWS('Dates')

OK, all set, now we have a table, some columns and a measure we can test with.

The Manual Way

The easiest way to introduce localization to your data model is to use Tabular Editor. In this case, I'll be using the free version of Tabular Editor, Tabular Editor 2.x. So, once installed, launch Tabular Editor from your External Tools ribbon in Power BI Desktop.

Right-click the Translations folder in the BIM viewer and choose New Translation:

And if a modified version of a certain INXS refrain plays in your head, you are not alone. Or, it's just me. In any event, in the Select Culture dialog, choose a language, such as de-DE - German (Germany):

OK, perfect, now you have new language translation in your model. The next part is really quite simple. Expand the Tables folder in the BIM viewer. Click the Dates table. In the properties panel under Translations, Perspectives, Security expand Translated Names.

OK, note that the Translations you have specified show up under Translated Names. The next part is pretty easy, you type in the translated names. For example, I typed in Dates next to English (United States) - (en-US). You go to Bing translate and get the word Dates in German and Spanish (Datteln and Fechas respectively) and type those in as well. It is pretty much rinse and repeat after that.

The Less Manual Way?

OK, turns out gopinathp2018ms has dones some work with localation of Power BI data models and has created some potentially helpful scripts to be used in Tabular Editor. You can find the scripts on GitHub here: gopinathp1978ms/PowerBI-Localization (github.com)

I did have some pains with the instructions however so I figured I would hit on a couple of pain points that I had. First, to use the scripts, you need to make sure that you enter a Translated Names for all your English translations. The GenerateResx.cs script seems to key off of the English name translation being present. So, no English translation, then your table, column or measure won't be included in the resx files generated. So, go do that but leave your other translations blank. Don't worry...I'll wait.

Back so soon? Great. Well, it really was a simple data model after all. In any event, go grab the GenerateResx.cs script from GitHub. While you are there, grab the Importtranslations.cs script for good measure.

OK, the next thing I recommed is to create yourself an empty directory to use when generating and importing your resx files. GenerateResx.cs doesn't seem to have an issue with other files being located in the same directory but Importtranslations.cs definitely does. I created a directory C:\temp\powerbi\Localization\res.

Alright, copy the script GenerateResx.cs. I used Notepad. Back in Tabular Editor, click the Advanced Scripting tab:

Paste the script code into the Advanced Scripting area. If you are not a coder, don't worry this next part is pretty easy. You have to edit the ReportName variable and the resxFilename variable which I have highlighted the exact portion you want to edit below:

Couple of notes. I honestly have no idea what happens if the ReportName is not set to the actual report file's name. Best to play it safe and just put the name of your file in there. For the resxFilename, make sure you have a trailing backslash ( \ ), otherwise, your files will not end up where you want and will instead be one directory higher and prefixed with whatever you had as the last part of your path. You an leave the CategoryName variable alone. But, if you choose to edit it, remember what you set it to because you will need to do the same thing in Importtranslations.cs.

Use the green triangle in the ribbon of the Advanced Scripting tab to run the script. If everything worked, you should get a screen that says "Resx files are generated". Click the Close button and go take a look in your folder:

OK, these resx files are really just XML files. If you don't know anything about XML, lucky you. In short, they are text files with structure. Easiest thing to do is to edit them in Visual Studio or some other tool that understands XML. But, you can use Notepad if you like sticking forks in your eye. Editing in Visual Studio looks like this:

Do not edit the Name or Comment columns. Just edit the Value column and put in the translation. As you can see in the image, I am editing the de-DE.resx file and putting in the German translations. Once you are finished, just save your file and exit. Editing these files in Visual Studio really is nicer and faster than in Tabular Editor, especially if you have a bunch of columns and measures. Plus, bonus, you could send just the resx file off for someone else to plug the translations in for you. 

Now, if you have been paying attention, you will notice that our table name Dates does not appear in the resx file. The scripts do not seem to deal with table names so you will just have to do those in Tabular Editor. And speaking of Tabular Editor, time to go back into Tabular Editor. But first, open up ImportTranslations.cs in Notepad or something and copy the script. Then paste the script into the Advanced Scripting area. I recommend Ctrl-A and then Ctrl-V so that any previous script gets replaced. Edit the ReportName and Translated_fileFolder variables to the same settings you used during GenerateResx.cs. If you messed with CategoryName, do that one too:

Use the green triangle in the ribbon of the Advanced Scripting tab to run the script. If everything worked, you should get a screen that says "All Languages files are imported". Click the Close button. Then in Tabular Editor check to see that your translations made it into the file. If everything is good, choose File and then Exit.

Conclusion

Localizing the names of tables, columns in measures in the Power BI data model is pretty easy to do even if it is fairly manual. Does it work? Who knows, I still haven't found a good way of testing it yet. I downloaded the Spanish language pack and all I managed to do was make my computer type double quotes when I was trying to type @ signs. So, there's that... If someone wants to test it, I have included the PBIX file below.

Localization.pbix

[DidierTerrien]

You may have a look at this solution with some automation for translations. More posts are available for visuals translations and translations life cycle management :

https://thebipower.fr/index.php/2020/04/12/automatic-translation-of-tabular-model/ 

[Greg_Deckler]

Part 2: Localizing the Data Model - Part 2 - Microsoft Power BI Community

Part 3: Localizing the Data Model - Part 3 - Microsoft Power BI Community