Dynamic Language Translation In Power BI

Problem statememt

There was requirement to see the product name in different languages as per region and country. But storing all the various language translation in database was not an option. The only option left was to do the language translation in Power BI on the fly. This document illustrates the steps to achieve dynamic translation in Power BI.

Solution Overview

This problem statement was solved by using following technologies

· Azure Cogitative Services (Language Translation API)

· Power BI Desktop Services

Following sections will explain the implementation in detail.

Azure coginative services

User should be register for “Azure Cogitative Services” to use the translation APIs.

data files used for exmaple

There are 3 files used for this example,

PRODUCT.txt: This file contains names of the product.

LANGUAGE.txt: This file contains language name and their code

PRODUCTSOLD.txt: this is sample file to test

Import these files in Power bi

Import these 3 files in the Power BI desktop.

cross join Product & language

The product and the language must be crossed join so that each product can be represented in all the available languages. For this will do little hack. Dummy column ‘CrossJoinHack’ with values as 1 was added in both the tables.

Once this is done, established relationship between the 2 tables

And now the language table can be merged to Product table to produce the cross join. This will bring the new column in the Product table.

Now expand the column and cross join will be produced in Product table. Rename ‘Language.Language’ to ‘Language’ & ‘Language.Code’ to ‘LanguageCode’

Important Step: calling congnitive service

To get each row of the product translated, cognitive services needs to be called. This service will be called in the custom column and 2 parameters ‘text’ & ‘to’ will be passed, where ‘text’ is text that needs to be translated (product name in this example) and ‘to’ is language code to which it needs to be translated.

Here is the code for the column. Click this link for API Reference. Please replace the keys with your Cognitive Service key.

each Table.FromColumns({Lines.FromBinary(

Web.Contents("https://api.microsofttranslator.com/V2/Http.svc/Translate?text=" & [ProductName] & "&to="&[LanguageCode], [Headers=[#"Ocp-Apim-Subscription-Key"="213e9cxxxxxxxxxxx63eaeef"]])

)})

In the below figure please note that query string is formed by using ProductName and LanguageCode from the available columns.