One request that comes up a lot is to be able to provide translations for the tables and columns of your model (especially here in Europe). Up until now this was not possible in Power BI as there was no UI for it. The new XMLA read/write feature changed all of this. As you probably know in SSAS Tabular this is already possible. The new XMLA endpoint makes any Power BI dataset into a SSAS tabular model with most of its functionalities. So how do you use it in Power BI and add translations? In this blog post I will walk you through it.
First, I have a simple model (yes Covid data) with a single table. When I use it in Power BI with my browser I get the model in English. In my example I want it to be shown in Dutch.
I will be using Tabular Editor to add the translations of the metadata. For any external tool to connect to datasets in premium you can use the XMLA endpoint. This needs to be enabled on your capacity by your admin. The dataset will live in an app workspace. You can find the endpoint to connect to it in the settings of the workspace.
After pressing Copy I can use that URL to connect to the “server” with my third-party tool. In this case I am using Tabular editor.
After connecting to the server I can select the dataset that I want to add my translations for. This might take some time as the capacity can have many many datasets. After selecting I can make any change to the model in Tabular Editor. In this case I choose to add a new translation.
Select the culture for the new translations.
Now in tabular editor you cannot add the translation itself, you will have to do this by hand. So we need to export the newly created translation metadata. Exporting creates a JSON file in TMSL structure that we can enhance.
In the file we can need to add the reference to the columns and provide its translations. There is not much documentation on it unfortunately. You can see an example of the structure below. There is also a little tool I created some time ago to easily add the translations. But to make this work you first need to have the structure set up like SSDT does on export.
After you made the changes and provided translations for your metadata you can import it again using tabular editor.
After all changes are imported you can save the model. This will immediately upload all the translations to the Power BI dataset and thus apply the translations.
When I log into my browser, which is set to Dutch locale, I can see the translations for the columns and tables I supplied:
Happy translating!
By the way, you will also be able to define translations in Power BI desktop using the method I described before here, but you will have to write all the translations yourself.
Hi Kasper, As of many europeans you said I’ve been waiting for this too. Does this fix working with translations in PowerBI (Pro) too? 1 language is working for me, but I want to use 4 languages and in that case I only see 1 language coming back. I am working with a live connection to on prem AS.
And for the translations cant you use the tabulartranslator of our Italian friends?
You need to use Power BI premium to set it up for now (at one point you will also be able to do this in dekstop or use my technique as described in a previous post). You cannot use AAS you need to use a dataset in Power BI to get it working as Power BI doesnt pass the locale to AAS but does to Power BI datasets. As an end user you dont need any special license.
Actually the tabular translator is based upon my own GitHub tool and that doesnt work when there is no base schema defined. SSDT creates this but tabular editor does not.
Hi Kasper thx. For the current client PowerBI premium is not (yet) an option. hopefully translations will be generally available soon, any idea if and when this is going to happen?
Hi Hans,
Did you get any update on translation In Power BI PRO mode? I am able to achieve it in Power BI Desktop but no in PRO.
You cannot yet, you need to host it on premium.
I believe you can also add translation values in the tabular editor GUI. There is a “Translations and Perspective” section at the bottom of the property list for each object with the relevant properties.
yes you can but the objects are not there yet. You need to add them by hand.
Hello Kasper,
Thank you for this solution.
There is an other solution using an Excel file instead of a JSON file to edit translations. Automatic translations are made in Excel and translations validation as well.
Please have a look at the video here : https://thebipower.fr/index.php/2020/04/12/automatic-translation-of-tabular-model/
Regards
Hi Kasper,
as Omri already mentioned, translations can be easily defined within Tabular Editor.
While translations already work in Power BI Desktop, they won’t show up in the service for Pro users.
Hopefully, that will change soon.
Hi Kasper,
As you mention, SSAS already have the possibility of translations. However, I have not succeeded in making this work in Power Bi Web. Do you know if it is possible, with a Power Bi report live connected to an SSAS model, and a premium subscription, to make translations work in Power Bi Web (App)?
It seems that it always chooses the english translation, regardless of the browser language.
Thank you for this great post!
I had successfully got translation working in service (Power BI Web) for end-users based on browser language. Added translation to existing model using Tabular Editor directly, by selecting column name and editing translation in properties panel without editing JSON.
Is there any update to this? I hear something is coming in Sept or Oct 2020 for metadata translations. Is this true?
Hi,
Txs for the post.
Is it possible to get it working in PowerBI embedded and AAS?
No matter what we do its always getting the English language from the languages that we have added with the Tabular editor etc…
It seems imposible to hack the connection to AAS so that a different language is used in the connection to the database.
No unfortunately not.