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 :

Download and try “PBI translations” report

Download

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 :

PBI translations scripts download

Download

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/

61 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??

      Reply

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

    Reply

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

    Reply

    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.

      Reply

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

    Reply

    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.

      Reply

    3. Is this excel plugin still available?? can we use the the above method for localization or there is any better way to this now?

      Reply

      1. Hello,
        The plug in is still available. According to my experience, this method is the only one which is totally free and maintainable. When we start such a project, we need to understand how it will evolve. What will happen when the model will change ? How much work new languages will cost ? So the maintainability is very important. I built this solution focusing on maintainability.
        The future of the solution will keep translations within Excel and will remove steps in Tabular Editor. It will be much easier and quicker.

        Reply

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

    Reply

    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.

      Reply

      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

        Reply

        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

          Reply

          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

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

    Reply

    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

      Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

    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

      Reply

      1. Hello FABIEN.
        I have exactly this problem today because I use power BI for the server. Please can you tell me how you managed to solve it?

        Greetings

        Reply

    2. Bonjour FABIEN.
      Je rencontre exactement ce problème aujourd’hui car j’utilise power BI pour le server. S’il vous plait pouvez vous me dire comment aviez vous réussi à le résoudre?

      Salution

      Reply

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

    Reply

    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

      Reply

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

    Reply

    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

      Reply

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

    Reply

    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

      Reply

      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?

        Reply

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

    Reply

    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.

      Reply

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

    Reply

    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

      Reply

      1. Monsieur TERRIEN bonjour!
        Je reviens vers vous car j’ai un phénomène qui se produit et dont j’aimerai savoir si celà est normal ou pas , je vous explique:
        Une fois mes traductions éffectués avec TE, mon rapport est publié , mais ce qui me parrait étrange comme comportement est que , une fois que je change la langue de mon navigateur pour qu’il affiche mon rapport dans une langue traduite , il me l’affiche correctement mais si j’actualise plusieurs fois mon navigateur il m’affiche parfois le rapport dans la langue par défaut (celle à partir de laquelle je traduis) et d’autres fois il m’affiche dans la lague demandé . Ce comportement est-il normal s’il vous plait? D’après ma propre analyse , je me dis que le phénomène est normal, car si le navigateur recoit plusieur fois la demande d’afficher une langue qui est déja affichée , il va retourner par moment l’affichage avec la lanque avec laquelle les métadonnées sont représentées dans le modèle (celle à partir de laquelle on renseigne les traduction). Qu’en penez vous s’il vous plait?

        Reply

        1. Bonjour Steve,
          La sélection de la langue des rapports doit être faite dans les paramètres du service Power BI. Près de l’image en haut, à droite, cliquer sur les trois points et sélectionner paramètres/paramètres/langue. En théorie, la langue sélectionnée dans le navigateur (ex Google Chrome) ne devrait pas intervenir.
          Le changement de langue intempestif n’est pas normal. Attention à s’assurer que le navigateur ne traduit pas les pages Web automatiquement.
          Cordialement

          Reply

          1. Effectivement Mr TERRIEN, je suis sur un rapport publié sur un serveur local (repport server ), donc le rafraichissement de ma page web traduit mon rapport par intermitence. Mais sur le service power bi, il fonctionne normalement . Malheuresement l’une des contrainte de déploiment du projet ne me permet pas d’utiliser la connexion internet , si oui la solution serait là! Donc je continu de cherhcer un moyen de faire fonctionner celà sur repport server normalement. Merci pour vos solutions Mr

  18. Hello Didier,
    j’ai une erreur lorsque j’essaie d’exécuter l’action “Save translatable objects”.
    Le fichier spécifié est introuvable.

    Une idée ?

    Reply

    1. Bonjour Yan,
      Il faut s’assurer que le chemin des fichiers est correct :

      // Modify files locations :
      var Objects_file = @”C:\Users\nom d’utilisateur\Desktop\PBI translations\TE objects.tsv”;
      var Excel_file = @”C:\Users\nom d’utilisateur\Desktop\PBI translations\PBI translations.xlsm”;

      Reply

      1. Bonjour Didier,
        j’ai vérifié le chemin des fichiers :
        // Modify files locations :
        var Objects_file = @”C:\Users\um2ya\Documents\3 – outils pbi traductions\trads\TE objects.tsv”;
        var Excel_file = @”C:\Users\um2ya\Documents\3 – outils pbi traductions\trads\PBI translations\PBI translations.xlsm”;

        Par contre je n’ai pas le fichier “PBI translations.xlsm”. Ce dossier est vide présentement. Est-ce normal ? Est-ce l’outil qui génère automatiquement ce fichier ou alors il faut le télécharger ? Le fichier “TE objects.tsv” existe bien et a été automatiquement généré.

        Merci d’avance pour ton retour.

        Reply

          1. je l’ai téléchargé et j’ai suivi les étapes , mais je n’ai malhereusement pas l’option power BI desktop file comme le montre la vidéo.
            Pouvez vous m’aidez s’il vous plait?

  19. Bonjour Didier . je n’ai pas l’option save transalatable objet comme vous au moment de faire la sauvegarde
    est ce normale? Si non comment puis je remedier à celà s’il vous plait ?
    merci d’avance

    Steve

    Reply

    1. Bonjour Steve,
      C’est expliqué dans le chapitre ‘Tabular Editor scripts’. Après avoir cliqué sur le bouton +, il faut donner un nom au script. Ce script apparaît alors en cliquant avec le bouton droit sur ‘Model’
      Cordialement

      Reply

  20. Hi Didier, Firstly I am still not getting T-PBI tab on my excel. But Using generated tsv file i am doing translation in excel using translation feature manually for each languages that is working fine. But when I load the tsv file using load script provided by you its loading the values but for Japanese language it is not giving actual results, basically tabular editor is not able to identify some languages or some some alphabets from French, Spanish, etc it shows ????? for all words in Japanese. So I am manually updating Japanese translations in tabular editor.

    Reply

  21. Sure Didier I will raise this issue. After researching on this issue I found something “Japanese characters often become garbled when you work with CSV or text files in Microsoft Excel. To avoid the problem, use the Unicode encoding option when saving the data into csv. Unicode is an international standard that includes support for most foreign alphabets.”
    Can you update the script export and import both so that it gets saved in Unicode option?
    Thanks.

    Reply

  22. Can you help me to update the script which saves the file in UTF- 8 encoding format so that it supports Japanese language when we load the translations from tsv to tabular editor.

    Reply

  23. Hi Didier,
    I can’t find the option to save translatable objects on my tabular editor, is there a reason for that? Kindly assist.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *