This blog will demonstrate how to perform language or text translation using Python and pipe it over into Power BI. It’s a cool technique that can make your reports more interesting. So let’s go and begin our Power BI Python tutorial. You can watch the full video of this tutorial at the bottom of this blog.
Game Of Thrones Dataset
Below is my Game of Thrones data set in Power BI with an Episode Name and IMDB Description at the top. We can see that that’s in English, but I’ve also created a bookmark that switches to Japanese, translated using a library in Python.
I will show you how to create a function that you can easily apply to other data sets. So let’s get started!
Power BI Python Tutorial: Transform data
We’re going to do all of the translation in the Script Editor, so we’ll start the process by opening the Power Query editor. After that, I’ll show you how to write the script below and transform its text using Python.
But before that, let’s look at the script below, where I will walk you through line by line to show you how this works.
The data set above has an episode number, number in season, episode name, director, and writer. If we scroll further to the right, it also has numerous other columns, like IMDB description and original air date.
You can apply a translation to the episode name but not for the writer as it’s a person’s name. So we will isolate a column for our translations and choose the IMD description column for this demonstration.
Transforming to Python Script
So let’s go over to Transform in the topleft ribbon and select Run Python Script at the top right. We’ll write everything within the script editor, although you can also do it in Jupyter Notebook and the like.
Next, let’s document our code. We can see above that it’s already indicating the dataset that holds the input data of our script.
Power BI Python Tutorial: Using Python Script
The image above is the Python script that we’ll be writing today. I’ll walk you through each step so that you will understand the whole process.
Import Translation
So let’s start by highlighting what we want to do: import our translation library from the TextBlob. Thus, we’ll write #import our translation library from TextBlob in the first part of our script.
Next, we want to create a function for translation so we’ll write # create a function for translation.
Defining the Function
We also need to define what the function is and to do that, we need to start with def followed by its name. In this case, let’s name it translation.
A function takes an amount of code or instructions that you can repeat. And similar to what you’ll see in Excel, it will feature an open and close parenthesis.
Our function only has one instruction and that is to translate a chosen text. Combining that with its name followed by a colon, we can write it as def translation (text).
After we put our colon, we will hit Enter and Tab to indent our instructions within the function. First, let’s create a variable called blob, representing our text.
And because we are using the text library, we will enter TextBlob after that. Next, we’ll pass it to the sentence we’re looking for, so we can write it as blob = TextBlob (sentence).
Now that the sentence is within the function, we can add more things to the mix. Let’s say we want to return the variable blob and translate that sentence (using the translate function) from English (indicated by en) to Japanese (indicated by ja).
For this function to work, we must ensure we are very explicit in saying translate that. But to be sure, we’re going to wrap it in a string (str). Now we can write this set of instructions as return str(blob. translate)from_lang =<‘ en’ to=’ja’)).
And what we get is the function below:
Translating the Column
We can now apply our newly created function to any text we’re looking for, which is to the IMDB description column of our data set. So in our Python script, we’ll write dataset (‘IMDB Description’) and then follow it with the apply function.
We’ll also put inside the apply function the translation function we’ve created and write all that as dataset (“IMDB Description’).apply(translation).
Because we want to create a new column, we’ll again use dataset, our table name, and add it on the function alongside an equal sign.
Power BI Python Tutorial: Running the Code
Now we have a new column to apply to our translation. So let’s run it by clicking OK and see if there are any issue.
Unfortunately, running the script will result in the error above. If we examine it closely, we can see that I used sentence instead of text as our placeholder.
That’s why it is important to always make sure to read your script, because it’s easy to make small mistakes in programming.
So we’ll return to the script, erase the sentence, and replace it with text. After that, we run the script again, click OK, and we’ll get the result below.
So what returns is a table that we can open up. After we click the table, it will direct us to our data frame below.
The data frame above is the Japanese translation of our IMDB Description. Notice that many of the names didn’t get translated, which is the right thing to do because not all of them can be translated into Japanese.
Other Languages
But aside from Japanese, you can also translate it into numerous other languages. You can see the complete list below. Please explore and see what kind of functionality you can create.
Let’s go back to our Power BI to see how easily I made this visual. The visual below is working off bookmarks in English and Japanese.
There are two bookmarks in this visual, and it’s hiding one of the tables. If I click a table under Selection, we can see in the image below that there are two tables.
I created this visual by attaching a button from the Format section to the English and Japanese translation options. The image below shows that the button has an action that links it to one of the bookmarks.
This button and its actions will easily let you change languages and use numerous functionalities. That means you can apply a function to the Episode Name and other columns from our dataset. And if you like, you can create a whole host of different languages to translate this into!
***** Related Links *****
Text Analysis in Python: An Introduction
How To Load Sample Datasets In Python
Python Scripting In Power BI Data Reports
Conclusion
Power BI has the ability to integrate directly with Python, taking its overall capabilities to another level. In this Power BI Python Tutorial, you learned how to use Run Python Script to translate texts and take advantage of both the Power BI’s interactive dashboard and Python’s flexibility.
With just ten lines of Python script, we were able to translate an entire column from English to Japanese. Use the same steps above to translate texts from other languages using the Python script to enhance your Power BI presentations!
