Multilingual reports in PowerBI Embedded
Creating a multilingual Power BI report is usually a frustrating experience as it’s not directly supported by the Power BI platform. In this scenario we are using the Power BI embedded Azure service and want to create a single report which is published in several languages automatically. Multilingual in this article refers to the report texts and field names but not to the data itself.
The solution I came up with was a command line tool called PowerPublisher which translates a single pbix report (so called template) into multiple pbix report files by replacing the placeholder texts in the template with their corresponding translations. The translations are read from an Excel file which also defines the languages. The same tool is also able to publish the newly created reports to a workspace in the Power BI Service.
Technical details
There are several articles explaining the pbix file structure which is basically a zip file containing files that construct the report. In this case however we are mostly interested of the Report/Layout-file which seems to contain all the texts in the visuals. You can edit the file manually but after modifications the PowerBI desktop complains the file being corrupted. You can get rid of the error by removing the SecurityBindings-file from the zip (guessing it contains a checksum of the package). You must also use the exact encoding the file is created with (UCS-2 LE without BOM) or you will get the same error.
The next step is the publishing of the report automatically to the PowerBI service. Luckily there is a documented import API (https://docs.microsoft.com/en-us/rest/api/power-bi/imports) for doing just that!
How to use the tool?
The first step is to create a report using the PowerBI Desktop and add all the required data sources and data to it. Do not add any visuals as this report is only a container for the dataset. When you are happy with the data you can publish the report to the PowerBI Service (eg. sales-data.pbix). In the service you will have two new items, the report and the corresponding dataset. The dataset is the one we want to keep and the empty report can be removed.
Next step is to create the actual report (e.g. sales.pbix) having all the visuals and use the online dataset from the first report as a data source (the data source type is Power BI dataset). In this report you should use placeholder texts that can be later translated using the PowerPublisher-utility. I have used ph_-prefix for those texts.
You will also need to create an Excel file containing all the translations as in the image below.
The last step is to run the utility which will generate a report file for each language by adding the language extension to the file (e.g. sales-fi.pbix). The tool will create as many files as you have languages defined in the translations file.
PowerPublisher translate sales.pbix translations.xlsx
You can optionally allow the tool to directly publish the language specific reports to your workspace in PowerBI. In that case you need to add configuration to appsettings.json to access the correct subscription and workspace.
PowerPublisher translate-publish sales.pbix translations.xlsx
The application is in no means a polished product but you can take the source and modify it for your needs. The source can be found from here.