Power BI · Translations

Implementing Data (As Well As Metadata) Translations In Power BI

Power BI Premium has supported metadata translations – translations for table, column and measure names etc – for a while now. Kasper has a great blog showing how to use this feature here; Tabular Editor makes it very easy to edit metadata translations too. However (unlike SSAS Multidimensional) Power BI doesn’t have native support for data translations, that’s to say translating the data inside your tables and not just the names of objects. There are some blog posts out there that describe ways of tackling this problem but none are really satisfying or reliable: techniques involving row-level security, for example, can force you to use relationships in a way that will impact performance; the undocumented UserCulture() DAX function isn’t ready and shouldn’t be used in production yet. In this blog post I’m going to describe a new approach to solving the problem of data translations that relies on the DirectQuery on Live connections functionality released in preview in December. It is far from perfect but I think it’s the best way of solving this problem available at the moment.

Describing the problem

The best way to understand the problem and what this new approach offers is to look at the end result. Here’s a Power BI report where everything is in English:

And here is exactly the same report where the same information is shown translated into German (apologies for the actual translations…):

There are several things to point out in this German version of the report:

  1. The text in the title at the top and in the sidebar is now in German
  2. The column headers for the matrix are now in German (this is what has been possible for a while with metadata translations)
  3. The dates are now formatted using the default for the German locale (again, as a result of metadata translations)
  4. The day names are now shown in German. This is the key thing here – the data in a column, as well as the metadata has been translated
  5. The decimal values in the Umsatz column are now formatted using the German locale so that a comma is used as the decimal separator and a full stop (ie a period for my American readers) is used as a thousands separator (again, as a result of metadata translations)
  6. The text indicating the total row is now in German (this is the normal behaviour of Power BI for users with a German browser locale)

So how do you get both data and metadata translations working in Power BI? Here’s a super-simple example showing how…

Step 1: The source dataset

First, let’s take a look at the dataset that contains all the data for both these reports. It contains two tables.

The Sales table looks like this:

Note that the Day Name column contains the names of the days of the week, and that there are two other columns containing the names of the days of the week translated to German and French. This is the table that holds the data shown in the matrix in the reports above.

There is also a table called Text that contains the text shown in the title and sidebar in the reports above. Again, there is a column containing the English text and two other columns containing German and French translations of that text:

There are no relationships between these tables:

Finally, this dataset also needs to be published to a workspace in the Power BI Service.

Step 2: Building the English version of the report

Building the original English version of the report is also quite straightforward. The matrix just contains data from the Sales table:

The only interesting thing is how the text in the title and sidebar is handled. In both cases I have used a card visual and dragged the Text column from the Text table into it, then filtered the data on the Visual column of the Text table so the appropriate text is shown:

In this case the sidebar shows the data from the Text column (aggregated to get the First value) where the Visual field contains the value “Textbox”.

Step 3: Creating the German translation dataset

This is where things get interesting. The next thing to do is to open a new .pbix file in Power BI Desktop, create a Live connection to the dataset created in step 1, and then hit the “Make changes to this model” button to create a new local dataset. This is where the new DirectQuery on Live connections functionality comes in; you should read the documentation on this feature before you go any further. The important thing to remember is that when you do this you are not duplicating any data or logic that is in the original dataset but you can make your own modifications to it.

There are two things that have to be done in this dataset. First, in Power BI Desktop, some renaming is necessary:

  1. The “Day Name” column on the Sales table has been renamed “English Day Name”
  2. The “German Day Name” column on the Sales table has been renamed “Day Name”
  3. The “Text” column on the Text table has been renamed “English Text”
  4. The “German Text” column on the Text table has been renamed “Text”

This results in the following columns in the local dataset:

Second, a translation object needs to be added to the dataset for the German (de-DE) locale for the metadata translations. I used Tabular Editor (instructions here) because it was the quickest and easiest way to add metada translations:

Note how the name of the Sales table has been translated to Umsatz, and how the names of the Date, Day Name (note: this is the column that has just been renamed as Day Name, which points to the German Day Name column in the original dataset) and Sales columns have been translated to Datum, Tagesname and Umsatz respectively.

This local dataset also needs to be published to the Power BI Service to proceed.

Step 4: building the German version of the report

The last thing to do is to go back to the original English version of the report built in step 2, open it in Power BI Desktop, then point it to the new German local dataset created in the previous step. You can do this by going to the Home tab in the ribbon, clicking on the Transform data button and then selecting Data source settings:

…and then selecting the German local dataset like so:

At this point you’ll see that the report is in a semi-translated state: the data has been translated but the metadata has not been.

Don’t panic though! Metadata translations can only be viewed in the browser or by changing the language settings inside Power BI Desktop so this is to be expected.

Notice that the middle column in the matrix points to a column called ‘Sales'[Day Name]. In the source dataset this contains the English day names; in the German translation dataset we have switched the names of the columns so ‘Sales'[Day Name] now contains the German day names. This is the key to solving the problem: all you need to do is ensure that each of the translation datasets you create exposes the set of table, column and measure names that your report expects; you just need to rename columns appropriately in each translation dataset so that they point to the columns in the source dataset that contain the correct translated names.

You should then save the .pbix file and publish again. You’ll either need to change the name of the report or publish it to a separate workspace; I recommend the latter, because it means you can tell all your German-speaking users to go to one workspace for their reports and your English-speaking users to go to another for their reports.

And that’s it – someone with a German browser locale viewing the version of the report connected to the German translation dataset will see this:


Here’s a diagram showing everything that has been built so far:

English-language users (who will have an English-language browser locale) use the original report that points to the source dataset. German-language users (who will have a German-language browser locale) use the German version of the report, which in turn connects to the German translation dataset; this gives them the German data and metadata translations.

The important things to remember are:

  • Even though you have multiple datasets there is no duplication of data or logic because of the way the new DirectQuery on Live connections functionality works.
  • Even though you have multiple copies of the same report for different languages, the report design in each case is identical and the only the dataset that each report points to is different.

As a result the effort needed to maintain multiple translated copies of the same report is kept to a minimum.

Not too far in the future there will be new and improved functionality in Power BI that makes solving this problem even easier, and at that point I’ll write a follow-up blog post.

17 thoughts on “Implementing Data (As Well As Metadata) Translations In Power BI

  1. Absolutely amazing this aproach, Until this article, and the funcionality of composite models imposible to have a good data translation without Mxn relations. thanks a lot.

  2. Great article thank you!
    This function is far from perfect though indeed, you are correct in stating that it is not ready for production.
    I find it strange that Power BI does not recognize it in the formula bar even though it gives you an output, kind of reminds me of REMOVEFILTERS() which was not recognized for some time but worked.

    Are those functions somewhat “in preview” or what is going on there?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      UserCulture() is not ready for use yet. It doesn’t work reliably in the Power BI Service – we still need to do some more development work to make sure it returns the results you’re expecting in all cases.

  3. Hi Chris!

    Nice article :). Is there any chance that the translation of MDX Metadata can be used in Power BI Report Server?

    Thank you very much and have a nice day


  4. Hi,
    Thank you a lot for this amazing article!
    Can you please tell me if metadata translations implemented on SSAS tabular instance should work on power BI desktop and Power Bi report server ( we use live connexion to SSAS instance)?


  5. Great article, thank you 🙂

    I wonder however, would it not be easier to use a query parameter to filter data tables for the right translation (each row with another translation).

    Then only the parameter would have to be changed before publishing each language version of a dataset, but no renaming of columns would be needed.

    The overall logic would remain the same.

  6. I like this approach however I can see one big caveat. If you have one dataset in the main language, then some child datasets in other languages. You have to create as many reports as you have languages, all pointing to a different “translated” dataset. If you have 5 languages and 10 reports, you have to maintain 50 reports…
    Also, you can’t dynamically change the language in a report (maybe with drill through to the same report in another language?).

  7. Thank you for this great article.

    I have been reading a lot of articles like this but non of them explain anything about how to setup or interact with Power BI Premium.

    We have a Premium Workspace at work, but it looks like this isn’t the only requirement, I need to know how to setup the Premium workspace or how to setup Power BI Desktop to take advantage of the Premium Workspace to make use of my translated Metadata.

    For example, if I start a brand new report and Get data from local csv files, I can open Tabular Editor or Metadata Translator and input my translations. But when I publish to my Premium Workspace, changing locale in in PBI Web, my browser or even my OS has no effect

    I suspect it’s because this isn’t how I’m supposed to do it but I cannot find any information on how to make this work.

    Setting up the Premium side and how to connect to I think this is one of the biggest, most important part of the whole procedure yet I cannot find an article that explains the “Metadata Translation is only possible on premium” line most articles mention.

    Should my dataset already be on the Premium workspace? I tried accessing a dataset that was already on the Premium workspace but I get a 401 error (The remote server returned an error:(401) Unauthorized). If this is the way, how do I fix this error?

    I’m sorry for the long question, I’ve been trying to figure this out for weeks and I can’t find anything.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      There’s a lot going on here. First of all, your dataset should already be in a workspace in a Premium capacity – where/when do you get the 401 error? Second, you need to change the locale in your browser to see the translated report; can you say exactly what you are changing when you try to view the translated report?

  8. Hi Chris, The ‘Total’ values in tables is not getting translated now the default value for total is “Total” under RowSubTotals. Is this some kind of bug in the current version?

  9. Hi Chris the “Total” values in both tables are not getting translated in current versions the default value “Total” is hardcoded under row subtotals. Is this a bug?

    1. Hey Akshay, the text ‘Total’ is automatically translated if it’s not hardcoded in the properties. If you haven’t hardcoded it, please verify with other languages, as in some languages, ‘Total’ is written as ‘Total,’ which might be causing confusion.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.