Automatic translation of tabular model

Automatic translation of tabular model

Automatic translation of tabular model using Tabular Editor and Excel is possible since the Power BI team gave read/write access to the XMLA endpoint. For the moment, it is a preview feature so be careful using it and work only on copies of your reports.

Automatic translation demonstration

Please watch this 5 mn video for an overview of the translation solution :

Tabular Editor scripts

These are scripts I wrote to save objects from Tabular Editor to a tsv file and to load translations from a tsv file to Tabular Editor. A tsv file is a text file containing data separated by a tab character :

Once the scripts are downloaded, you can load them in Tabular editor this way :

  • Save the downloaded scripts in a folder
  • Open “Advanced Scripting” tab, then click on the “Open a C# script from a file” and select a downloaded script
automatic translation
  • Modify the path in variables below “// Modify files locations”
  • Click on “+” button
  • Give a name to the custom action and check at least the “Model” checkbox then click “Ok” button
  • Click on the “Play” button when you want to execute the script or right click on the “Model” in the tree and select ” Custom actions/your custom action name”
  • Redo all these steps each time you get a new version of the scripts !

When you execute the “Save translatable objects” script, translatable objects are exported to a tsv file which is used by the Excel file.

You can get the Excel file in this blog post : https://thebipower.fr/index.php/2020/05/10/automatic-translation-of-visuals-labels/

References

Tabular Editor is a powerful tool for tabular model edition created by Daniel Otykier : https://tabulareditor.com/
Detailed presentation : https://powerbi.tips/2020/04/tabular-editor-rocks/

Supported languages and countries/regions for Power BI : https://docs.microsoft.com/en-us/power-bi/supported-languages-countries-regions

Watch this video from Ted Pattison which brings many information, tools, tips and ressources. Power BI Dev Camp Session 12 Building Multi Language Reports in Power BI : https://youtu.be/-yK-0P5-J38

Limitations

Model name and hierarchies levels are not translated for the moment but might be in the future

Warning

Since March 2021, Power BI desktop doesn’t reflect model translations. Thus, they might appear once the report is published

See also :
Visuals labels translations in Power BI reports : https://thebipower.fr/index.php/2020/05/07/visuals-labels-translations-in-power-bi-reports/
Automatic translation of visuals labels : https://thebipower.fr/index.php/2020/05/10/automatic-translation-of-visuals-labels/

38 Comments

    1. The option to Save Translatable Objects is not available in the available free version.
      You’re using 2.9.7, the latest free version on the website is 2.17.1 (and Github).
      I thought version 2 was free but it looks like your version isn’t available??

  1. Hi Didier

    Very good post
    So, I think we can very soon deploy a dataset (version new metadata) to Azure Analysis Service ?
    I do not have access to custom action -> save translatable object -> opening of excel, when I am on the model
    Need help please ;>)

    Best regards

  2. Hello,
    That’s a great post.
    Thank you for sharing.
    I have requirement where the report must be translated to different languages including column headers, tool tips,titles etc..
    I think you method can help me for column headers.
    Can you please share the excel sheet you used for the translation purpose

    1. Thank you,
      This method can help translating the whole report (visuals, model, data).
      Please visit this post as a starting point to understand the whole project : https://thebipower.fr/index.php/2020/07/05/translations-life-cycle-management/

      You need to consider Automatic translation of tabular model is in preview because XMLA endpoint read/write is a preview feature in Power BI. Please be carefull at the moment.
      Files and the Excel plugin are in this post : https://thebipower.fr/index.php/2020/05/10/automatic-translation-of-visuals-labels/
      The post above will be updated during next weeks with an how to. You can share your experience and let me know about potential improvements.

  3. All works great in Desktop. Is there any reason it doesn’t work in the Workspace after publishing? – I changed every locale setting, Windows, Workspace, Browser and it still wouldn’t change when running the report from a Workspace and this was on a pbix that would change in the Desktop successfully, but the Workspace copy only changes the Power BI standard titles not the field titles.

    1. Hello,
      Did you activate the preview feature “enhanced metadata format” in your report ? If it still doesn’t work, I suppose it is because the XMLA endpoint is still a preview feature. We need to be patient.
      You can check your translations in tabular editor. If they are correct then there is nothing else to do.
      To change the language in the service, you only need to select the language in “Parameters / Language” in Power BI service.

  4. When I try to “Translate All Lines”, I get a error stating that I need to check my internet connection…however the internet is connected and I can download and browse.

    Can you please help with a solution?

    1. Hello,
      Do you have Internet explorer installed ? If yes, which version ? Can you try Google translate within Internet explorer ? May be you have a proxy or VPN ? Is your connection slow ?
      Please try from another machine / another network and let me know.

      1. Hello Didier

        I appreciate your response.

        I do have Internet Explorer installed, version 11.572.19041.0. I have no VPN and my internet connection is good.

        I have tried another network an it is still the same. I only have the one pc unfortunately.

        The error persists. All I can think of is that there is a neccessary premission that I need to change. Apologies if I am annoying XD

        1. Hello,
          You are not ennoying at all. That’s good to know some users experience problems.
          I released version 1.0.4 of the plugin. I hope it will solve your issue. Please let me know

          1. Hi Didier

            I installed the new plugin version but no result. I appreciate the support. I did a manual translation through Google translate.

            Overall the plugin is awesome!
            Thanks

  5. Hola!
    No tengo acceso a la acción personalizada -> guardar objeto traducible -> apertura de Excel, cuando estoy en el modelo
    ¿es necesario Power BI Premium?

    muchas gracias

    1. Hello,
      Premium is not necessary.
      I understand you cannot save the objects with a script in Tabular Editor. Am I right ? Did you follow the steps above in chapter “Tabular Editor scripts” ?
      In order to investigate further, please send me screenshots and explain how to reproduce the error
      Regards

  6. Outstanding, you saved me a lot of time (in the middle of very short deadlines).

    I love how Microsoft is bridging the gap between SSAS and PBI, but they are doing a poor job documenting it. If not because of your effort, I wouldn’t have known.

  7. Hello,
    Thank’s a lot for this excellent tip.
    Does-it work after publishing in power BI report server?
    I think that, until today, SSAS (tabular) translations are only supported in power BI premium, and work on power BI Desktop.

  8. Hello,
    Thank you. I don’t know about Power BI report server. It does work on the service and it worked on the desktop. Recently there was a regression on the desktop preventing translations to work but I guess it will be corrected in future versions.

  9. Hi Dider. first of all. thank you for your post on Translation. It help us a lot.
    We use powerbi for server and we met some issue. Maybe you can help us.
    When I deploy the example report you provide on our PowerBI/SSRS Portal and i run it.
    Normally the translation must be in the same as the browser language.
    It’s not the case.
    Ex: If the report as two translations , en-US and fr-FR . on the portal sometime the translation appears in En-US and if I immediately refresh it the translations go to fr-FR. I test it on IE , Firefox and edge with the Same issues.
    So I like your method to manage translation but unfortunately for the moment we can’t use it in production.
    Thanks to help us if you can

    1. Hello Fabien,
      Thanks for your interest in this post. I feel translations is a very important topic but at the moment, the experience in Power BI is not consistent. For example, translations could be seen in the desktop before March 2021. Since then, they are not visible.
      I don’t have an SSRS installation for tests. So I can give you my thoughts about it but I’m not sure.
      – I don’t feel it is necessary to change the browser language. In Power BI service, you need to change the language in parameters/general/language
      – I am not sure if SSRS supports translations and the right way to swith cultures

      I suggest you try the same report in Power BI service. Otherwise, you may tweet a question or ask to Guy in the cube during their weekly video and check carefully what other people chat about it.
      Let me know the results of you investigations please.
      Regards

  10. First of all,
    Congratulations and thank you so much.
    You have done such an amazing job, actually it is a life-saver job!

    May I ask you one think?
    All translation process has been worked fine for me.
    My point is,
    After all the work finished, if I open Tabular Editor (from Power BI), use the script “Custom Action/Save translatable objects.cs”, it works good, consecutively it generates a file called “TE objects.tsv” filled up with all the informations/translations (language by language)… ok.

    After that,
    In Excel (“PBI translations.xlsx”) opened, I click in “T-BI-P”, open file (“TE objects.tsv”), and finally I click in “Load Objects”, all the objects (measures, columns, etc.) are opened fine, except the translation columns (en-US, fr-FR, etc.), all these columns still blank (as the first time).
    As I told before, if I open the file “TE objects.tsv” by nopad++, all the translations generated by this code “sb.Append(Measure.TranslatedNames[Language.Name]);” is there.

    Thus,
    Am I facing (creating) an issue, or this behavior is normal?
    If so, there is any way to open the previous translations (created in the past) in xls file by excel throw “TE objects.tsv”?

    Thanks in advance.

    1. Hello Gustavo,
      Thank you for your nice comments and your detailed description of the issue.
      The concept of the plugin is to keep reference translations into Excel. Each time the model is modified, the ‘Load objects’ menu will add new objects coloured in yellow and modified objects coloured in grey so you know which translations to add and to remove.
      As the reference is in Excel, I didn’t import translations from the model. It might be an option to add in the future. My plan is to move the features to Power BI Sidetools so the process will be much simpler as it can read and write the model.
      As a workaround, you can copy/paste translations from the tsv file to the xlsx file the first time you update translations. The next time, you will just need to import objects.
      Hope it helps

  11. Hi Didier

    I hope you are well. I have the latest version of Power BI desktop installed. When I run the process above and change the translation in tabular editor , it updates the table names in the tabula editor view only. Even when saving changes to the model and opening the file in the regional settings I applied the translation to, only the file ribbon changes langage and not the actual items

    1. Hello,
      As mentioned in the warning section, “Since March 2021, Power BI desktop doesn’t reflect model translations. Thus, they might appear once the report is published”. It’s a bug

  12. Hi Didier

    Thank you for replying. I have published the file and set the lanuage of my browser to french, however the changes are not taking effect

    1. Hello,
      You need to change the language in Power BI service settings using the steering wheel (top right close to the search box). It’s located in the ‘General tab’ under the ‘Language’ section

      1. Awesome, thank you, would this not then translate the whole tenant?

        So other users in the tenant might then see the service in French?

  13. Hi,

    Does this still work? Just downloaded the sample .pbi file…tried to set language in both pbi desktop and pbi service to French (France) and still seeing only english…

    On Version: 2.99.862.0 64-bit (November 2021).

    Thanks.

    1. Hello Victor,
      At the moment, there are regressions in Power BI desktop and the service which prevent translation working properly. The solution I propose is still valid. I continue working on this solution in order to make it much simpler to use.

  14. Hello Didier,

    your Video helped me a lot. Thank you very much! My report shows the translation in Service like you mentioned before. That works really nice!
    I´m facing the problem that I now translated my originally German Reports into English, but can´t make it switch back to German again for German users.

    I tried a lot. Changed language settings in Desktop & Service, saved the file in Tabular Model, published it multiple times and so on, but nothing works.
    I can see the German version in my PowerBI Desktop, because it didn´t do the translation here from the Beginning on.
    However, in the Service I can only see the English version. I don´t have the Premium Version 🙁
    If you have some recommendation for me, what to try I´d be very happy!

    Thanks in advance!
    Best regards,
    Marie

    1. Hello Marie,
      Thank you to use this solution.
      Translations are not shown in the desktop. It’s a “regression by design” confirmed by Chris Wade. If you see German in the desktop, I suppose you renamed columns in your model.
      According to my experience, pro workspaces show only English translations and premium workspaces show translations depending on the language selected in service parameters.
      Do you confirm my assumptions ? If I’m right, you have the choice to switch to a premium workspace or to choose one language at the moment. Power BI team works actively on translations:
      https://powerbi.microsoft.com/fr-fr/blog/userculture-dax-function-now-supported-in-power-bi-premium/
      Translations in composite models have been announced recently

Leave a Reply

Your email address will not be published.