Understanding Power BI Streaming Scripts (Python)

Deep Dive: Understanding Power BI Streaming Scripts (Python)

In previous blog posts, we've explored how to set up Power BI streaming datasets and dashboards. Now, let's go through the Python scripts used to push that real-time data, line by line. This will give you a deep understanding of how they simulate live data and stream it to Power BI.

🧩 There are two scripts:

Script 1: stream_to_powerbi.py (for random temperature/humidity — IoT-style data)
Script 2: simulate_stocks.py (for stock-like price streaming of AAPL, MSFT, GOOGL)

Let’s explain both in detail:

📄 1. `stream_to_powerbi.py`

(Simulates IoT-style data: temperature, humidity, timestamp)

Code Snippet 1: Imports

import requests
import json
from datetime import datetime
import time
import random

requests: This module is essential for making HTTP requests (like sending data to a web server). We'll use its post() method to send data to Power BI.
json: Power BI's streaming API expects data in JSON format. This module helps convert Python dictionaries and lists into JSON strings.
datetime: Used to get the current date and time, which is crucial for the timestamp field in our data.
time: Provides functions for time-related tasks, specifically time.sleep() to pause the script for a specified duration between data pushes.
random: Used to generate random numbers for simulating sensor readings (temperature and humidity).

Code Snippet 2: Power BI Push URL

PUSH_URL = "https://api.powerbi.com/beta/your-dataset-id/rows?key=your-key-here"

This line defines a constant variable, PUSH_URL, which stores the unique URL provided by Power BI when you create a streaming dataset. This URL acts as the API endpoint where your Python script will send the data.

Code Snippet 3: Data Generation Loop

while True:
    temperature = round(random.uniform(20.0, 35.0), 2)
    humidity = random.randint(40, 90)
    timestamp = datetime.now().isoformat()

while True:: This creates an infinite loop, ensuring the script continuously generates and sends data, simulating a live data stream.
temperature = round(random.uniform(20.0, 35.0), 2): Generates a random floating-point number between 20.0 and 35.0 (inclusive) for temperature, rounded to two decimal places.
humidity = random.randint(40, 90): Generates a random integer between 40 and 90 (inclusive) for humidity.
timestamp = datetime.now().isoformat(): Captures the current local date and time and converts it into a standard ISO 8601 format string, which is easily parsable by Power BI.

Code Snippet 4: Data Payload Preparation

    data = [{
        "temperature": temperature,
        "humidity": humidity,
        "timestamp": timestamp
    }]

This line creates a Python list containing a single dictionary. The keys of this dictionary ("temperature", "humidity", "timestamp") must exactly match the field names defined in your Power BI streaming dataset schema. This structure is required by the Power BI streaming API for pushing rows.

Code Snippet 5: Sending Data via HTTP POST

    response = requests.post(PUSH_URL,
                             data=json.dumps(data),
                             headers={"Content-Type": "application/json"})

requests.post(PUSH_URL, ...): This is the core of the streaming. It sends an HTTP POST request to the PUSH_URL.
data=json.dumps(data): The data parameter holds the payload. json.dumps(data) converts our Python list of dictionaries into a JSON formatted string, which is what the Power BI API expects.
headers={"Content-Type": "application/json"}: This header tells the Power BI API that the body of the request is in JSON format.

Code Snippet 6: Logging and Delay

    print(f"Sent: {data} | Status: {response.status_code}")
    time.sleep(5)

print(...): Displays the sent data and the HTTP status code received from Power BI (e.g., 200 for success), helping you monitor the script's activity.
time.sleep(5): Pauses the script for 5 seconds. This controls the frequency of data pushes to Power BI.

🟡 Outcome:

Every 5 seconds, Power BI receives new sensor-like data (temperature, humidity, timestamp). You can visualize this live using streaming tiles or a push dataset report within Power BI.

📄 2. `simulate_stocks.py`

(Simulates stock-like prices for AAPL, MSFT, GOOGL)

Code Snippet 1: Imports

import requests
import json
import time
import random
from datetime import datetime

These are the same imports as in stream_to_powerbi.py, serving the same purposes for HTTP requests, JSON handling, time, and random number generation.

Code Snippet 2: Power BI Push URL

PUSH_URL = "https://api.powerbi.com/beta/your-dataset-id/rows?key=your-key-here"

Similar to the previous script, this is your Power BI push URL, specifically for the streaming dataset configured with symbol, price, and timestamp fields.

Code Snippet 3: Initial Stock Prices

stocks = {
    "AAPL": 190.50,
    "MSFT": 330.25,
    "GOOGL": 2800.40
}

This dictionary holds the initial simulated prices for each stock symbol. These values will be updated in subsequent iterations to simulate price fluctuations.

Code Snippet 4: Price Simulation Function

def simulate_price(current_price):
    change_percent = random.uniform(-0.002, 0.002)
    return round(current_price * (1 + change_percent), 2)

This function takes a current_price as input.
It generates a small random percentage change (between -0.2% and +0.2%).
It then calculates the new price by applying this percentage change and rounds the result to two decimal places, mimicking real stock price precision.

Code Snippet 5: Main Loop for Stock Data Generation

while True:
    data = []
    timestamp = datetime.utcnow().isoformat() + "Z"

    for symbol in stocks:
        new_price = simulate_price(stocks[symbol])
        stocks[symbol] = new_price

        data_point = {
            "symbol": symbol,
            "price": new_price,
            "timestamp": timestamp
        }

        data.append(data_point)

while True:: An infinite loop for continuous streaming.
data = []: Initializes an empty list to hold the data points for all stocks in the current second.
timestamp = datetime.utcnow().isoformat() + "Z": Gets the current Coordinated Universal Time (UTC) and formats it as an ISO 8601 string with a 'Z' suffix, which is standard for global timekeeping and good practice for financial data.
for symbol in stocks:: This loop iterates through each stock symbol (AAPL, MSFT, GOOGL) defined in the stocks dictionary.
new_price = simulate_price(stocks[symbol]): Calls our simulate_price function to get a new price for the current stock.
stocks[symbol] = new_price: Updates the price in the stocks dictionary. This ensures that the next time the loop runs for this stock, its price will fluctuate from this new value.
data_point = {...}: Creates a dictionary for the current stock's data point, matching the Power BI dataset schema.
data.append(data_point): Adds the generated data point to the data list. After this loop completes, the data list will contain a data point for each stock for the current timestamp.

Example `data` content after the loop:

[
  { "symbol": "AAPL", "price": 190.80, "timestamp": "2023-10-27T10:30:00.000Z" },
  { "symbol": "MSFT", "price": 330.55, "timestamp": "2023-10-27T10:30:00.000Z" },
  { "symbol": "GOOGL", "price": 2801.12, "timestamp": "2023-10-27T10:30:00.000Z" }
]

Code Snippet 6: Sending Data and Error Handling

    try:
        response = requests.post(PUSH_URL,
                                 data=json.dumps(data),
                                 headers={"Content-Type": "application/json"})
        print(f"Sent: {data} | Status: {response.status_code}")
    except Exception as e:
        print(f"Error pushing data: {e}")

try...except: This block is used for error handling. If the requests.post() call encounters an issue (e.g., network error, invalid URL), it will catch the exception and print an error message instead of crashing the script.
requests.post(...): Sends the prepared JSON data to Power BI, similar to the previous script.
print(...): Logs the sent data and the HTTP status code.

Code Snippet 7: Delay

    time.sleep(1)  # Send every second

time.sleep(1): Pauses the script for 1 second. This creates a high-frequency update stream, ideal for simulating stock tickers.

🟢 Outcome:

You get new stock prices every second for AAPL, MSFT, and GOOGL in Power BI. You can chart them, use slicers, and simulate interactive stock dashboards.

🎯 Key Concepts Recap

Here's a summary of the core concepts used in both scripts:

Concept	Explanation
`random.uniform()` & `random.randint()`	Simulates sensor readings or price fluctuations by generating random numbers within a specified range.
`datetime.now().isoformat()` / `datetime.utcnow().isoformat() + "Z"`	Generates ISO 8601 formatted time strings, which is a standard and robust way to represent timestamps for data ingestion. Using UTC is best for global data like stocks.
`requests.post()`	The fundamental method for pushing data to the Power BI streaming endpoint via an HTTP POST request.
Infinite loop (`while True`) + `time.sleep()`	Simulates a continuous data feed, essential for real-time streaming, by repeatedly generating and sending data with a controlled delay.
JSON format (via `json.dumps()`)	The required data format for ingestion by the Power BI streaming API. Python dictionaries are converted to JSON strings.
`try...except` block	Basic error handling to gracefully manage potential issues during data transmission (e.g., network problems, API errors) without crashing the script.

Search This Blog

RR Education: Power BI Advanced