CODEWITHSUNDEEP
pythonpowerbi-desktoppostal-code
DataAnalyst
DataAnalyst

Reputation: 117

PowerBI - Python to Extract City, Country from Postal Code

I have a column in PowerBI Dashboard named, "Postal Codes" Can I use a python library within PowerBI to write a script and extract city, state, and country names.

Here is what I have tried so far, I am using ODBC to connect to the database. I have a sample query which returns 49,445 rows

SELECT
ID,
POSTAL_CODE
FROM EMPLOYEE_DATA;

Here is my python script which is part of my PowerBI Report

# 'dataset' holds the input data for this script

# Import the required library
from geopy.geocoders import Nominatim
from geopy.exc import GeocoderTimedOut

# Function to extract city, state, and country from postal code
def extract_location(POSTAL_CODE):
    geolocator = Nominatim(user_agent="geoapi")
    try:
        location = geolocator.geocode(POSTAL_CODE, timeout=10)  # Adjust the timeout value as needed
        if location:
            return location.address.split(", ")[-3:]
        else:
            return ["", "", ""]  # Return empty strings if location not found
    except GeocoderTimedOut:
        return ["", "", ""]  # Retry logic: return empty strings if geocoding times out


output = dataset.apply(lambda row: extract_location(row['POSTAL_CODE']), axis=1, result_type='expand')

# Rename the output columns
output.columns = ['City', 'State', 'Country']

Upvotes: 5

Views: 260

Answers (1)

davidryan
davidryan

Reputation: 2305

Two libraries that are useful here:

Upvotes: 1

Related Questions