Unlock the Power of Python in Power BI: Automating Language Detection!

[Bibiano_Geraldo]

Power BI is an exceptional tool for data analysis, but its true potential is unleashed when we integrate Power Query with Python. This combination allows us to automate tasks, enrich data, and conduct advanced analyses effortlessly. In this blog, we’ll walk you through a real-world use case that showcases the power of Python in Power Query: automatic language detection for customer reviews!

🚀 Why Use Python for Language Detection in Power BI?

Power Query provides robust transformation capabilities, but it lacks built-in language detection features. Python, however, offers powerful libraries that make this process straightforward. By combining both, we can automatically determine the language of text-based data within Power BI.

🛠️ Prerequisites: Enabling Python in Power BI

Before we begin, ensure that Python scripting is enabled in Power BI:

1. Open Power BI Desktop.

2. Go to File > Options and Settings > Options.

3. Under Global > Python scripting, select your Python installation path.

4. Click OK.

For more details, refer to the official guide: Run Python Scripts in Power BI Desktop

Our Problem: Automatic Language Detection

Imagine we have a database of customer reviews written in different languages. For more advanced analysis, we want to automatically identify the language of each comment. We can use Python to solve this problem easily. Let's go step by step!

🔧 Step 1: Install Required Python Libraries

We’ll need two key libraries:

• langdetect for detecting languages
• pycountry for mapping language codes to full names

To install them, open your command prompt and run:

pip install langdetect pycountry

📊 Step 2: Sample Data (Customer Reviews)

Imagine we have the following dataset of customer reviews: (Excel file attached):

Our goal is to automatically detect the language of each comment.

📥 Step 3: Importing Data into Power Query

1. Load your dataset into Power BI.
2. Navigate to Power Query Editor.
3. Click Transform Data > Run Python Script.

🐍 Step 4: Writing the Python Script for Language Detection

In the Python script editor within Power Query, insert the following code:

from langdetect import detect, DetectorFactory
import pycountry

DetectorFactory.seed = 0  # Ensures consistent results

def detect_language(text):
    try:
        lang_code = detect(str(text))  # Convert to string to avoid errors
        language = pycountry.languages.get(alpha_2=lang_code)  # Get full language name
        return language.name if language else "Unknown"
    except:
        return "Unknown"

dataset["Language"] = dataset["Comment"].astype(str).apply(detect_language)
dataset[["Customer", "Comment", "Language"]]

Click OK and Power Query will execute the script.

📊 Step 5: Expanding the Output Table

After running the script, Power Query initially returns a single row containing a table object. To extract the results:

• Click on the "Table" value in the output.

🎯 Final Output:

🎯 Conclusion: The Power of Python in Power Query

This example illustrates how Python extends Power Query’s capabilities, making it possible to detect languages automatically within Power BI. The same approach can be applied to:

✅ Sentiment Analysis (positive/negative reviews)

✅ Automatic Translations

✅ Text Summarization

🔥 Try it out in your next Power BI project and unlock new analytical possibilities!