Python Script to get Data for Power BI

For a recent meetup I wanted to query PredictHQ. They have a dataset of events and I wanted to bring it into Power BI. The snag: Power BI isn’t compatible with the API this provider uses. It responds with a 308 Permanent Redirect, which needs a further request, and Power Query only handles redirects up to 307.

Python was the way round it. The Python web client has no problem handling 308 redirects, and Power BI can use a Python script as a data source.

Python code

I wrote and tested the code in VS Code. For Power BI to run it you need the pandas and matplotlib packages installed.

import requests
import matplotlib
import pandas as pd

response = requests.get(
    url="https://api.predicthq.com/v1/events",
    headers={
        "Authorization": "Bearer $accesscode$",
        "Accept": "application/json"

    }, params={"limit": "50"}
)

data = response.json()

res = data['results']
category = list(map(lambda x: x['category'], res))
country = list(map(lambda x: x['country'], res))
description = list(map(lambda x: x['description'], res))
duration = list(map(lambda x: x['duration'], res))
end = list(map(lambda x: x['end'], res))
entities = list(map(lambda x: x['entities'], res))
firstseen = list(map(lambda x: x['first_seen'], res))
iid = list(map(lambda x: x['id'], res))
labels = list(map(lambda x: x['labels'], res))
location = list(map(lambda x: x['location'], res))
entity_id = list(map(lambda x: x[0]['entity_id'] if x else '', entities))
formatted_address = list(map(lambda x: x[0]['formatted_address'] if x else '', entities))
venue_name = list(map(lambda x: x[0]['name'] if x else '', entities))
venue_type = list(map(lambda x: x[0]['type'] if x else '', entities))

df = pd.DataFrame(list(zip(
    category, country, formatted_address, venue_name, venue_type,  description, duration, end, firstseen, labels,)),
    columns=['category', 'country', 'formatted_address', 'venue_name', 'venue_type', 'description', 'duration', 'end', 'firstseen', 'labels'])

print(df)

Get Data

With the data looking right, open Power BI and choose Get Data, then Python script. Paste the script in and Power BI runs it, returns the data and offers Load or Transform. I did a little more cleaning in Power Query for the nested list elements.

Wrapping it up

Extending Get Data with Python means you don’t have to learn custom connector development. It would be better still if Microsoft handled 308 responses natively; the RFC was published in 2015, and more providers are adopting it. If you’re an MVP, it’s worth raising.