Power BI Construction Dashboard + AI

Build a Power BI dashboard with AI-generated project summaries via DeepSeek.

Introduction

30 Second Summary

Construction firms track dozens of projects at once, each with shifting budgets, timelines, and risk levels. Turning that raw data into clear executive insights is what separates a good BI analyst from a great one.

In this project, you will build a Power BI dashboard that displays construction project KPIs and uses a Python script to call the DeepSeek API for AI-generated executive summaries of each project's health.

What You'll Build

A Power BI Desktop dashboard simulating the business intelligence environment of a construction firm, with an integrated AI analysis pipeline that generates narrative summaries via a vendor-agnostic Python module.

By the end of this project, you'll have:

  • A Python script (construccion_ai.py) that generates sample construction data and calls DeepSeek for AI analysis.
  • A Power BI dashboard with KPI cards, budget charts, status breakdowns, and an AI insights panel.
  • A vendor-agnostic config pattern that makes the AI provider swappable between DeepSeek and NVIDIA NIM.
  • Secret Mission: Make the solution multi-provider by adding NVIDIA NIM as an alternative backend, demonstrating vendor flexibility.

Are there any prerequisites?

You need Python installed on your machine, a DeepSeek API key with available balance, and a Windows device (Power BI Desktop is Windows-only). For the Secret Mission, you'll also need an NVIDIA Developer account.

Before We Start

Before We Start...

Install Power BI Desktop and Configure Python

Your dashboard needs two things to work: a visualization tool that can display data, and a scripting environment that can fetch AI-generated insights.

In this step, you will install Power BI Desktop and connect it to your Python installation. This gives Power BI the ability to run Python scripts that pull data and call APIs directly inside your reports.

In this step, get ready to:
  • Install Power BI Desktop on your Windows machine.
  • Enable Python scripting inside Power BI Desktop.
  • Install the pandas and requests Python packages.
Download and install Power BI Desktop

Power BI Desktop is a free Windows application for building interactive data visualizations. You can install it from the Microsoft Store (recommended for automatic updates) or from the Microsoft Download Center.

  • Open the Microsoft Store on your Windows machine by pressing the Windows key and typing Microsoft Store.
  • In the Microsoft Store search bar, type Power BI Desktop and press Enter.
  • Click Install on the Power BI Desktop page.
  • Wait for the download and installation to complete.

Why install from the Microsoft Store?

The Microsoft Store version updates automatically in the background. You do not need admin privileges to install it. If you prefer the direct download instead, visit the Microsoft Download Center and download the 64-bit PBIDesktopSetup_x64.exe installer.

  • Once installation finishes, press the Windows key, type Power BI Desktop, and press Enter to open it.
  • If a Welcome screen or sign-in dialog appears, close it by clicking the X in the corner. You do not need a Microsoft account for this project.

You should see the Power BI Desktop home screen with a blank canvas and the ribbon menu at the top.

Power BI Desktop won't open?

Make sure you are running Windows 10 or Windows 11 (64-bit). Power BI Desktop does not run on macOS, Linux, or Windows Server.

If you see a blank white screen, try reinstalling WebView2 from the Microsoft download page, then relaunch Power BI Desktop.

help me troubleshoot Power BI Desktop not opening on my Windows machine

Enable Python scripting in Power BI

Power BI can run Python scripts to import and transform data. You need to tell Power BI where your Python installation lives so it can execute scripts.

  • In Power BI Desktop, click File in the top-left corner of the ribbon.
  • Click Options and settings.
  • Click Options.
  • In the left sidebar of the Options dialog, scroll down and click Python scripting.
  • Under Detected Python home directories, check if Power BI has automatically detected your Python installation. If it shows the correct path, you are good to go.
  • If it shows Other or no path, click Browse and navigate to the folder where Python is installed (for example, C:\Users\YourName\AppData\Local\Programs\Python\Python311 or C:\Python311).
  • Click OK to save the settings.

Find your Python path quickly

Open a terminal and run where python (Windows). The output shows the full path to your Python executable. The folder one level above (without python.exe) is what Power BI needs.

💡 Why does Power BI need my Python path?

Power BI runs Python scripts by calling python.exe directly from the directory you specify. It only imports pandas DataFrames back into the data model. Without a valid path, Power BI cannot execute any Python code.

Install the required Python packages

Your Python scripts in Power BI will use pandas for structuring data into DataFrames and requests for calling the DeepSeek API over HTTP.

  • Open a terminal by pressing the Windows key, typing cmd, and pressing Enter.
  • Install both packages by running this command:
pip install pandas requests

What does this command do?

  • pip install downloads and installs Python packages from PyPI.
  • pandas is the data manipulation library that Power BI uses to receive script output.
  • requests is the HTTP library you will use to call the DeepSeek API.
  • Verify both packages installed correctly by running:
python -c "import pandas; import requests; print('OK')"
  • You should see OK printed in the terminal with no errors.

Seeing 'pip is not recognized'?

This means Python's Scripts folder is not in your system PATH. Try running python -m pip install pandas requests instead. If that also fails, reinstall Python from python.org and check the box that says Add Python to PATH during installation.

help me fix pip not being recognized in my Windows terminal

Your environment is ready. Power BI Desktop can now execute Python scripts, and your packages are in place to generate data and call AI APIs. Next up, you will create the construction dataset that powers your dashboard.

Create the Construction Dataset with Python

Your Power BI Desktop is configured and Python scripting is enabled. Now you have the foundation to connect Python-generated data directly to your dashboard.

Every dashboard starts with data. You'll write a Python script that generates realistic construction project metrics for a firm in Los Cabos, then import that data directly into Power BI's data model using its built-in Python connector.

In this step, get ready to:
  • Write a Python script that generates a construction project DataFrame.
  • Import the dataset into Power BI using a Python script data source.
  • Format columns for currency, percentage, and whole number display.
Create the Python script with construction data

You'll create a Python script called construccion_ai.py that generates a pandas DataFrame with simulated construction project data. This script will serve as the data backbone for your entire dashboard.

  • Open a terminal by pressing the Windows key, typing PowerShell, and pressing Enter.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir construccion-dashboard
cd construccion-dashboard
  • Create the Python script file by running:
New-Item construccion_ai.py
  • Open the file in Notepad by running:
notepad construccion_ai.py
  • Paste the following imports and configuration block at the top of the file:
import pandas as pd
import requests
import json

# === CONFIGURATION ===
DEEPSEEK_API_KEY = "your-deepseek-api-key-here"
DEEPSEEK_BASE_URL = "https://api.deepseek.com"
DEEPSEEK_MODEL = "deepseek-v4-flash"

What does this code do?

The imports bring in pandas for data manipulation, requests for making API calls later, and json for handling API response data.

The configuration section sets up your DeepSeek API credentials as variables. You'll use these in the next step when you add AI analysis. For now, they're placeholders ready for when you connect the AI.

  • Below the configuration block, paste the data generation function and main execution:
# === DATA GENERATION ===
def generate_construction_data():
    projects = [
        {"proyecto": "Villa Marina Residencial", "presupuesto": 4500000, "gastado": 3200000, "completado": 71, "dias_restantes": 45, "estado": "en-tiempo"},
        {"proyecto": "Hotel Costa Azul", "presupuesto": 12000000, "gastado": 9800000, "completado": 82, "dias_restantes": 30, "estado": "en-tiempo"},
        {"proyecto": "Plaza Comercial San Jose", "presupuesto": 8000000, "gastado": 7200000, "completado": 60, "dias_restantes": 90, "estado": "retrasado"},
        {"proyecto": "Condominios La Jolla II", "presupuesto": 6500000, "gastado": 6400000, "completado": 55, "dias_restantes": 120, "estado": "en-riesgo"},
        {"proyecto": "Centro Medico Cabo", "presupuesto": 3200000, "gastado": 1800000, "completado": 45, "dias_restantes": 60, "estado": "en-tiempo"},
        {"proyecto": "Residencial Palmeras", "presupuesto": 5800000, "gastado": 5100000, "completado": 88, "dias_restantes": 15, "estado": "en-tiempo"},
        {"proyecto": "Oficinas Transpeninsular", "presupuesto": 2800000, "gastado": 2900000, "completado": 65, "dias_restantes": 75, "estado": "en-riesgo"},
        {"proyecto": "Desarrollo Turistico Bahia", "presupuesto": 15000000, "gastado": 8500000, "completado": 40, "dias_restantes": 180, "estado": "retrasado"},
    ]
    return pd.DataFrame(projects)

# === MAIN EXECUTION ===
# Generate data
df_projects = generate_construction_data()

What does this code do?

The generate_construction_data() function creates a list of 8 construction projects with realistic data for a firm in Los Cabos. Each project has a name, budget, amount spent, completion percentage, days remaining, and a status indicator.

The status values (en-tiempo, retrasado, en-riesgo) represent on-time, delayed, and at-risk projects. The main execution line calls the function and stores the resulting DataFrame in df_projects, which is what Power BI will detect and import.

  • Save the file in Notepad with Ctrl+S.
  • Test the script by running it in your terminal:
python construccion_ai.py

You should see no errors. The script runs silently because it generates the DataFrame in memory without printing it.

  • Verify the data by running a quick print check:
python -c "exec(open('construccion_ai.py').read()); print(df_projects)"

You should see a table with 8 rows showing all project columns: proyecto, presupuesto, gastado, completado, dias_restantes, and estado.

Seeing a ModuleNotFoundError?

If you see an error about pandas not being found, make sure you installed it in Step 1. Run pip install pandas and try again. If you have multiple Python versions, ensure you're using the same one configured in Power BI.

Help me fix my Python import errors

✔️ Awesome, I've got everything!

Great. Make sure you've saved construccion_ai.py and keep Notepad open. You'll add the AI analysis function in the next step.

ⓧ I'd like to double check the full code

import pandas as pd
import requests
import json

# === CONFIGURATION ===
DEEPSEEK_API_KEY = "your-deepseek-api-key-here"
DEEPSEEK_BASE_URL = "https://api.deepseek.com"
DEEPSEEK_MODEL = "deepseek-v4-flash"

# === DATA GENERATION ===
def generate_construction_data():
    projects = [
        {"proyecto": "Villa Marina Residencial", "presupuesto": 4500000, "gastado": 3200000, "completado": 71, "dias_restantes": 45, "estado": "en-tiempo"},
        {"proyecto": "Hotel Costa Azul", "presupuesto": 12000000, "gastado": 9800000, "completado": 82, "dias_restantes": 30, "estado": "en-tiempo"},
        {"proyecto": "Plaza Comercial San Jose", "presupuesto": 8000000, "gastado": 7200000, "completado": 60, "dias_restantes": 90, "estado": "retrasado"},
        {"proyecto": "Condominios La Jolla II", "presupuesto": 6500000, "gastado": 6400000, "completado": 55, "dias_restantes": 120, "estado": "en-riesgo"},
        {"proyecto": "Centro Medico Cabo", "presupuesto": 3200000, "gastado": 1800000, "completado": 45, "dias_restantes": 60, "estado": "en-tiempo"},
        {"proyecto": "Residencial Palmeras", "presupuesto": 5800000, "gastado": 5100000, "completado": 88, "dias_restantes": 15, "estado": "en-tiempo"},
        {"proyecto": "Oficinas Transpeninsular", "presupuesto": 2800000, "gastado": 2900000, "completado": 65, "dias_restantes": 75, "estado": "en-riesgo"},
        {"proyecto": "Desarrollo Turistico Bahia", "presupuesto": 15000000, "gastado": 8500000, "completado": 40, "dias_restantes": 180, "estado": "retrasado"},
    ]
    return pd.DataFrame(projects)

# === MAIN EXECUTION ===
# Generate data
df_projects = generate_construction_data()
Import the dataset into Power BI

Power BI Desktop can run Python scripts directly as a data source. You'll paste your data generation code into Power BI's Python script connector, and it will import the resulting DataFrame as a table in your data model.

  • Switch back to Power BI Desktop.
  • In the Home ribbon at the top, click Get data.
  • In the Get Data dialog, select Other from the categories on the left.
  • Select Python script, then click Connect.

Why Python script instead of a file?

Power BI's Python script connector runs your code every time you refresh the data. This means when you later add the AI analysis function, a single data refresh will regenerate both the project metrics and the AI summaries automatically.

  • In the Python script dialog, paste the following code into the Script field:
import pandas as pd

# Generate construction project data for Power BI
def generate_construction_data():
    projects = [
        {"proyecto": "Villa Marina Residencial", "presupuesto": 4500000, "gastado": 3200000, "completado": 71, "dias_restantes": 45, "estado": "en-tiempo"},
        {"proyecto": "Hotel Costa Azul", "presupuesto": 12000000, "gastado": 9800000, "completado": 82, "dias_restantes": 30, "estado": "en-tiempo"},
        {"proyecto": "Plaza Comercial San Jose", "presupuesto": 8000000, "gastado": 7200000, "completado": 60, "dias_restantes": 90, "estado": "retrasado"},
        {"proyecto": "Condominios La Jolla II", "presupuesto": 6500000, "gastado": 6400000, "completado": 55, "dias_restantes": 120, "estado": "en-riesgo"},
        {"proyecto": "Centro Medico Cabo", "presupuesto": 3200000, "gastado": 1800000, "completado": 45, "dias_restantes": 60, "estado": "en-tiempo"},
        {"proyecto": "Residencial Palmeras", "presupuesto": 5800000, "gastado": 5100000, "completado": 88, "dias_restantes": 15, "estado": "en-tiempo"},
        {"proyecto": "Oficinas Transpeninsular", "presupuesto": 2800000, "gastado": 2900000, "completado": 65, "dias_restantes": 75, "estado": "en-riesgo"},
        {"proyecto": "Desarrollo Turistico Bahia", "presupuesto": 15000000, "gastado": 8500000, "completado": 40, "dias_restantes": 180, "estado": "retrasado"},
    ]
    return pd.DataFrame(projects)

# Execute - Power BI imports this DataFrame
df_projects = generate_construction_data()

Why not paste the entire file?

You only paste the data generation portion into Power BI. The API configuration and requests import aren't needed for this import. You'll create a separate data source for the AI analysis in Step 4.

  • Click OK to run the script.
  • The Navigator window appears showing df_projects in the left pane. Select the checkbox next to df_projects to preview the data in the right pane.
  • Click Load to import the table into your data model.

Power BI loads the 8 construction projects into your data model. You should see df_projects appear in the Data pane on the right side of the screen.

Navigator shows no tables?

Make sure your script ends with the line df_projects = generate_construction_data(). Power BI only detects pandas DataFrames that are assigned to variables at the script's top level.

Also verify that your Python scripting path in File > Options and settings > Options > Python scripting points to the same Python installation where pandas is installed.

Help me troubleshoot my Power BI Python script import

Verify and format column types

Power BI auto-detects column types from the DataFrame, but you should verify and format them for proper display in your dashboard visuals.

  • Click the Table view icon in the left sidebar of Power BI Desktop (it looks like a grid).
  • Select the df_projects table from the Data pane on the right.
  • Click the presupuesto column header to select it.
  • In the Column tools tab in the ribbon, click the Data type dropdown and select Fixed decimal number (Currency).
  • Repeat this for the gastado column. Select it and set Data type to Fixed decimal number.
  • Click the completado column header. Set its Data type to Whole number.
  • Click the dias_restantes column header. Set its Data type to Whole number.

Why format these columns?

Setting the correct data types ensures Power BI displays budget values with proper currency formatting in charts. It also enables correct aggregation. Without this, Power BI might treat numeric columns as text and refuse to sum or average them.

  • Verify your table shows 8 rows with the columns: proyecto (text), presupuesto (currency), gastado (currency), completado (whole number), dias_restantes (whole number), and estado (text).
  • Save your Power BI file by pressing Ctrl+S. Name it construccion-dashboard.pbix and save it inside your construccion-dashboard folder on the Desktop.

Data type dropdown is grayed out?

Make sure you're in Table view (not Report view) and that you've clicked directly on the column header to select it. The Column tools tab only appears when a column is actively selected.

Help me format my columns in Power BI

Your data layer is ready. Next up, you'll build the AI analysis script that sends each project's KPIs to DeepSeek and returns executive summaries.

Build the AI Analysis Script

Your construction dataset is loaded into Power BI and looking great. Now comes the part that sets this dashboard apart from a standard BI report.

You are going to write a Python function that sends each project's KPIs to the DeepSeek API and receives back an executive-level narrative summary in Spanish. This is the AI layer that transforms raw numbers into actionable insights.

In this step, get ready to:
  • Write an API call function that sends project data to DeepSeek and returns a summary.
  • Create a wrapper function that iterates over all projects and builds a summaries DataFrame.
  • Test the complete script locally to confirm AI-generated summaries return correctly.
Write the API call function

Each construction project has KPIs like budget, spend, and completion percentage. The get_ai_summary function packages these KPIs into a prompt, sends them to DeepSeek's chat completions endpoint, and returns the AI-generated narrative.

  • In construccion_ai.py, find the line that says # === MAIN EXECUTION ===.
  • Add the following code above that line (leave a blank line between the new section and # === MAIN EXECUTION ===):
# === AI ANALYSIS ===
def get_ai_summary(project_row, api_key, base_url, model):
    # Build a prompt with the project's key metrics
    prompt = f"""Analiza este proyecto de construccion y genera un resumen ejecutivo de 2 oraciones en espanol:
- Proyecto: {project_row['proyecto']}
- Presupuesto: ${project_row['presupuesto']:,.0f} MXN
- Gastado: ${project_row['gastado']:,.0f} MXN
- Completado: {project_row['completado']}%
- Dias restantes: {project_row['dias_restantes']}
- Estado: {project_row['estado']}

Incluye una recomendacion accionable para el director de obra."""

    # Set up authentication headers
    headers = {
        "Content-Type": "application/json",
        "Authorization": f"Bearer {api_key}"
    }

    # Configure the API request payload
    payload = {
        "model": model,
        "messages": [
            {"role": "system", "content": "Eres un analista senior de proyectos de construccion. Responde de forma concisa y profesional."},
            {"role": "user", "content": prompt}
        ],
        "max_tokens": 200,
        "stream": False
    }

    # Send the request and handle errors gracefully
    try:
        response = requests.post(
            f"{base_url}/chat/completions",
            headers=headers,
            json=payload,
            timeout=30
        )
        response.raise_for_status()
        result = response.json()
        return result["choices"][0]["message"]["content"]
    except Exception as e:
        return f"Error al generar analisis: {str(e)}"

What does this code do?

The prompt is an f-string that injects each project's real numbers (budget, spend, percentage complete, days remaining, status) into a structured request. The system message tells DeepSeek to respond as a senior construction analyst.

The function uses the Authorization: Bearer header pattern for authentication. The try/except block ensures that if any API call fails, the script continues with an error message instead of crashing.

💡 Why an OpenAI-compatible format?

DeepSeek uses the same request structure as OpenAI's API (messages array, model field, max_tokens). This means the same code works with any OpenAI-compatible provider. You only need to change the base URL, API key, and model name to switch providers.

  • Save construccion_ai.py.

Seeing a syntax error on the f-string?

The triple-quoted f-string (f"""...""") must not have extra indentation on the first line after the opening quotes. Make sure Analiza starts immediately after the opening f""".

help me fix a syntax error in my Python f-string for the DeepSeek prompt

Create the wrapper function

A single function handles one project. You need a wrapper that loops over the entire DataFrame, calls the AI for each row, and assembles the results into a new DataFrame that Power BI can import.

  • In construccion_ai.py, add the following function directly below the get_ai_summary function (leave a blank line between them):
def generate_ai_summaries(df, api_key, base_url, model):
    # Iterate over each project and collect AI summaries
    summaries = []
    for _, row in df.iterrows():
        summary = get_ai_summary(row, api_key, base_url, model)
        summaries.append({
            "proyecto": row["proyecto"],
            "ai_resumen": summary,
            "proveedor": "DeepSeek"
        })
    return pd.DataFrame(summaries)

What does this code do?

The generate_ai_summaries function loops through every row of the projects DataFrame using iterrows(). For each project, it calls get_ai_summary and appends the result to a list.

The returned DataFrame has three columns: proyecto (project name for linking back to the main table), ai_resumen (the AI narrative), and proveedor (which AI provider generated it). The provider column will become useful in the Secret Mission.

  • Save construccion_ai.py.

Indentation error after pasting?

Make sure generate_ai_summaries is at the same indentation level as get_ai_summary (no extra indent). Both functions should start at column 0.

help me fix an indentation error in my Python script

Test the script locally

Before importing into Power BI, you want to confirm that your script actually calls DeepSeek and returns clean results. Running it in the terminal gives you immediate feedback.

  • In construccion_ai.py, find the # === MAIN EXECUTION === section. It currently has one line:
# === MAIN EXECUTION ===
df_projects = generate_construction_data()
  • Add the following code below df_projects = generate_construction_data() to call the AI analysis and print the results for testing:
# Generate AI summaries using DeepSeek
df_ai = generate_ai_summaries(
    df_projects,
    api_key=DEEPSEEK_API_KEY,
    base_url=DEEPSEEK_BASE_URL,
    model=DEEPSEEK_MODEL
)

# Temporary print for local testing (remove before Power BI import)
print(df_ai)

What does this code do?

This calls generate_ai_summaries with your configuration constants. It sends all 8 projects to DeepSeek and stores the resulting summaries DataFrame in df_ai. The print(df_ai) line is temporary so you can see the output in your terminal.

  • Make sure you have replaced your-deepseek-api-key-here at the top of the file with your actual DeepSeek API key.
  • Save construccion_ai.py.
  • Run the script in your terminal by running this command:
python construccion_ai.py

The script takes about 20-25 seconds to run (it makes 8 API calls, one per project). You should see a DataFrame printed with columns proyecto, ai_resumen, and proveedor. Each row contains a 2-sentence Spanish executive summary for that project.

  • After confirming the output looks correct, remove the print(df_ai) line from your script. Power BI does not need it.
  • Save construccion_ai.py one final time.

Seeing an authentication error?

If you see Error al generar analisis: 401 in the output, your API key is incorrect. Double check that DEEPSEEK_API_KEY at the top of your file contains your real key (not the placeholder text).

If you see a timeout error, your network may be blocking the request. Try again or check your internet connection.

help me debug my DeepSeek API connection error

✔️ Awesome, I've got everything!

Great. Double check you have saved your file with the print(df_ai) line removed.

ⓧ I'd like to double check the full code

Here is the complete construccion_ai.py at this point:

import pandas as pd
import requests
import json

# === CONFIGURATION ===
DEEPSEEK_API_KEY = "your-deepseek-api-key-here"
DEEPSEEK_BASE_URL = "https://api.deepseek.com"
DEEPSEEK_MODEL = "deepseek-v4-flash"

# === DATA GENERATION ===
def generate_construction_data():
    projects = [
        {"proyecto": "Villa Marina Residencial", "presupuesto": 4500000, "gastado": 3200000, "completado": 71, "dias_restantes": 45, "estado": "en-tiempo"},
        {"proyecto": "Hotel Costa Azul", "presupuesto": 12000000, "gastado": 9800000, "completado": 82, "dias_restantes": 30, "estado": "en-tiempo"},
        {"proyecto": "Plaza Comercial San Jose", "presupuesto": 8000000, "gastado": 7200000, "completado": 60, "dias_restantes": 90, "estado": "retrasado"},
        {"proyecto": "Condominios La Jolla II", "presupuesto": 6500000, "gastado": 6400000, "completado": 55, "dias_restantes": 120, "estado": "en-riesgo"},
        {"proyecto": "Centro Medico Cabo", "presupuesto": 3200000, "gastado": 1800000, "completado": 45, "dias_restantes": 60, "estado": "en-tiempo"},
        {"proyecto": "Residencial Palmeras", "presupuesto": 5800000, "gastado": 5100000, "completado": 88, "dias_restantes": 15, "estado": "en-tiempo"},
        {"proyecto": "Oficinas Transpeninsular", "presupuesto": 2800000, "gastado": 2900000, "completado": 65, "dias_restantes": 75, "estado": "en-riesgo"},
        {"proyecto": "Desarrollo Turistico Bahia", "presupuesto": 15000000, "gastado": 8500000, "completado": 40, "dias_restantes": 180, "estado": "retrasado"},
    ]
    return pd.DataFrame(projects)


# === AI ANALYSIS ===
def get_ai_summary(project_row, api_key, base_url, model):
    prompt = f"""Analiza este proyecto de construccion y genera un resumen ejecutivo de 2 oraciones en espanol:
- Proyecto: {project_row['proyecto']}
- Presupuesto: ${project_row['presupuesto']:,.0f} MXN
- Gastado: ${project_row['gastado']:,.0f} MXN
- Completado: {project_row['completado']}%
- Dias restantes: {project_row['dias_restantes']}
- Estado: {project_row['estado']}

Incluye una recomendacion accionable para el director de obra."""

    headers = {
        "Content-Type": "application/json",
        "Authorization": f"Bearer {api_key}"
    }

    payload = {
        "model": model,
        "messages": [
            {"role": "system", "content": "Eres un analista senior de proyectos de construccion. Responde de forma concisa y profesional."},
            {"role": "user", "content": prompt}
        ],
        "max_tokens": 200,
        "stream": False
    }

    try:
        response = requests.post(
            f"{base_url}/chat/completions",
            headers=headers,
            json=payload,
            timeout=30
        )
        response.raise_for_status()
        result = response.json()
        return result["choices"][0]["message"]["content"]
    except Exception as e:
        return f"Error al generar analisis: {str(e)}"


def generate_ai_summaries(df, api_key, base_url, model):
    summaries = []
    for _, row in df.iterrows():
        summary = get_ai_summary(row, api_key, base_url, model)
        summaries.append({
            "proyecto": row["proyecto"],
            "ai_resumen": summary,
            "proveedor": "DeepSeek"
        })
    return pd.DataFrame(summaries)


# === MAIN EXECUTION ===
df_projects = generate_construction_data()

df_ai = generate_ai_summaries(
    df_projects,
    api_key=DEEPSEEK_API_KEY,
    base_url=DEEPSEEK_BASE_URL,
    model=DEEPSEEK_MODEL
)

Your AI analysis pipeline is working. Next up, you will connect this script to Power BI so the AI summaries appear as a table in your data model.

Connect AI Analysis to Power BI

Your AI analysis script works locally and produces executive summaries for each construction project. The next step is to bring those AI-generated insights directly into your Power BI data model so they live alongside your KPI data.

In this step, you'll import the AI summaries as a second table, link it to your project data through a relationship, and display the narratives in a visual on your report canvas.

In this step, get ready to:
  • Import the AI summaries DataFrame into Power BI as a second data source.
  • Create a relationship between the project data and AI summaries tables.
  • Add a Table visual displaying AI narratives alongside project names.
Import the AI summaries DataFrame into Power BI

Power BI can run your full Python script as a data source. The script generates both DataFrames, and Power BI lets you choose which ones to load through the Navigator window.

  • In Power BI Desktop, click Get data in the Home ribbon.
  • Select Other from the left sidebar, then select Python script.
  • Click Connect.
  • A script editor window opens. Open your construccion_ai.py file, select all its contents, and paste them into the script editor.
  • Click OK to run the script.

What happens when the script runs?

Power BI executes your Python script and detects all pandas DataFrames created during execution. The Navigator window shows each DataFrame as a loadable table. Your script produces two: df_projects and df_ai.

The script calls the DeepSeek API for each project, so it takes approximately 15-25 seconds to complete. This is well within Power BI's 30-minute script timeout.

  • In the Navigator window, check the box next to df_ai to select it.
  • Leave df_projects unchecked since you already have that table from the earlier import.
  • Click Load.

After loading completes, you should see df_ai appear in the Fields pane on the right side of Power BI Desktop. It contains three columns: proyecto, ai_resumen, and proveedor.

Script taking too long or showing an error?

  • Make sure your DEEPSEEK_API_KEY in the pasted script is your actual key, not the placeholder text.
  • Verify your internet connection is active. Power BI's Python environment needs network access to reach the DeepSeek API.
  • If you see a module error, confirm that pandas and requests are installed in the same Python installation that Power BI is configured to use (check File > Options and settings > Options > Python scripting).

Need more help? Why is my Python script failing in Power BI?

Create a relationship between the tables

A relationship tells Power BI how to connect rows across two tables. Both df_projects and df_ai share a proyecto column with matching project names. Linking them lets you build visuals that pull data from both tables at once.

  • Click the Model view icon on the left side of Power BI Desktop (it looks like two boxes connected by a line).
  • You should see both df_projects and df_ai displayed as table cards on the diagram canvas.
  • Drag the proyecto column from df_projects onto the proyecto column in df_ai.

A solid line appears between the two tables, indicating an active relationship. Since each project name is unique in both tables, Power BI sets the cardinality to one-to-one.

Why create a relationship?

Without a relationship, Power BI treats the two tables as unrelated data. You could not filter AI summaries by project status or combine KPI metrics with narrative text in the same visual.

The relationship on proyecto tells Power BI that "Villa Marina Residencial" in df_projects is the same entity as "Villa Marina Residencial" in df_ai. Filters applied to one table propagate to the other.

🙋‍♀️ Don't see both tables in Model view?

  • Confirm that you loaded df_ai in the Navigator (not just previewed it). Return to Get data > Other > Python script and re-run the import if needed.
  • If the tables appear but the drag doesn't create a line, make sure both columns are named exactly proyecto with matching text values.

Still stuck? Help me create a relationship between my tables in Power BI.

Add a Table visual displaying AI summaries

Now that the tables are linked, you can build a visual that shows AI-generated narratives next to project identifiers. A Table visual works best here because the AI summaries are multi-sentence text that needs horizontal space.

  • Click the Report view icon on the left side of Power BI Desktop (the top icon in the left pane).
  • Click an empty area on the report canvas.
  • In the Visualizations pane on the right, click the Table icon (the grid symbol).
  • In the Data pane (also called Fields pane), expand df_ai and check the boxes for proyecto and ai_resumen.
  • Drag the bottom-right corner of the visual to make it wider so the narrative text is readable without excessive wrapping.

How do I make the text more readable?

  • With the Table visual selected, click the Format your visual icon (paintbrush) in the Visualizations pane.
  • Expand Column headers > Text and turn on Text wrap so long column names display fully.
  • Expand Values > Text and enable Text wrap for the cell content as well.

Your report canvas now shows a Table where each row displays a project name and its AI-generated executive summary. The summaries are in Spanish and include actionable recommendations for the construction director.

Table shows columns but no data?

  • Check that the relationship is active by switching to Model view. You should see a solid line (not dashed) between the tables.
  • Make sure you selected fields from df_ai (not df_projects) in the Data pane.
  • If ai_resumen shows error messages instead of summaries, your API key was likely invalid during the Power BI script run. Re-run the data source by right-clicking the table and selecting Refresh data.

Need help? Why does my Table visual show errors instead of AI summaries?

Your data model now has AI-powered narratives linked to project KPIs. Next up, you'll build the full executive dashboard with charts, cards, and an interactive AI insights panel.

Build the Executive Dashboard

Your data model is connected, and AI summaries are flowing into Power BI. Now it is time to build the visual layer that transforms raw numbers into a decision-making tool.

In this step, you will create the KPI cards, comparison charts, and an interactive AI Insights panel that makes your dashboard worthy of an executive presentation.

In this step, get ready to:
  • Create KPI Card visuals and a budget comparison bar chart.
  • Add a status Donut chart and a timeline bar chart.
  • Build an AI Insights panel with conditional formatting and slicer interaction.
Create KPI cards and a budget comparison chart

Executives scan dashboards for headline numbers first. Card visuals surface these single-number KPIs instantly.

  • Click on an empty area of the report canvas so no existing visual is selected.
  • In the Visualizations pane on the right, select the Build visual tab, then click the Card visual icon.
  • From the Data pane, expand df_projects and drag presupuesto into the Values field well.
  • The card now displays the sum of all project budgets. Resize it by dragging the corner handles so it fits neatly in the top-left area of your canvas.

What is a Card visual?

A Card visual displays a single aggregated data point prominently. It is the Power BI equivalent of a headline number on a report. You will create one card per KPI.

  • Repeat the same process to create three more Card visuals for these fields:
  • gastado (shows total spent across all projects).
  • completado (shows the average completion percentage). Click the dropdown arrow on the field in the Values well and change the aggregation to Average.
  • estado (shows the count of at-risk projects). After adding the field, click the dropdown arrow on estado in the Values well and select Count. Then add a visual-level filter: in the Filters pane for this visual, drag estado to Filters on this visual, expand it, and check only en-riesgo.
  • Arrange the four cards in a row across the top of the canvas.
  • Now add the budget comparison chart. Click an empty area of the canvas.
  • In the Visualizations pane, select the Clustered bar chart icon.
  • Drag proyecto to the Y-axis well.
  • Drag presupuesto to the X-axis well.
  • Drag gastado to the X-axis well (it appears as a second bar alongside presupuesto).
  • Position this chart below the KPI cards on the left side of the canvas.

Why a clustered bar chart?

Placing presupuesto and gastado side by side for each project makes over-budget situations jump out instantly. Any project where the gastado bar exceeds the presupuesto bar is a red flag.

You should now see four KPI cards at the top and a budget vs. spent bar chart below them.

Card shows a strange aggregation?

If your completado card shows a sum instead of an average, click the dropdown arrow on the field in the Values well and change the aggregation to Average.

Help me fix my Card visual aggregation

Add the status donut chart and timeline chart

Knowing how many projects fall into each status category helps leadership allocate resources. A donut chart makes proportions obvious at a glance.

  • Click an empty area of the report canvas.
  • In the Visualizations pane, select the Donut chart icon.
  • Drag estado to the Legend well.
  • Drag proyecto to the Values well. Click its dropdown arrow and select Count to count projects per status.
  • Position the donut chart on the right side of the canvas, next to the bar chart.

The donut now shows three slices representing en-tiempo, retrasado, and en-riesgo.

  • Next, add the timeline chart. Click an empty area of the canvas.
  • In the Visualizations pane, select the Clustered bar chart icon.
  • Drag proyecto to the Y-axis well.
  • Drag dias_restantes to the X-axis well.
  • Position this chart below the donut chart on the right side of the canvas.

What does this chart show?

This bar chart acts as a simplified Gantt-style timeline. Longer bars mean more days remaining, so you can quickly see which projects are closest to their deadline.

Your canvas now has six visuals: four KPI cards, a budget comparison chart, a donut chart, and a timeline chart.

Donut chart shows only one slice?

Make sure estado is in the Legend well, not the Values well. The Legend field splits the donut into segments by category.

Help me fix my Donut chart segments

Add the AI Insights panel with conditional formatting and slicer

The final piece ties everything together. A slicer lets executives pick a project, and the AI Insights table updates to show that project's narrative summary. Conditional formatting makes risk levels visible through color.

  • Select the existing AI summaries Table visual on your canvas.
  • Resize and reposition it to sit in the lower portion of the canvas. This becomes your AI Insights panel.
  • In the Visualizations pane, confirm the Columns section contains proyecto and ai_resumen from df_ai. If not, drag them there now.
  • Also add estado from df_projects to the Columns section. Because the tables are related by proyecto, Power BI resolves this automatically.

Now apply conditional formatting so rows are color-coded by status.

  • With the Table visual selected, go to the Visualizations pane.
  • In the Columns section, click the dropdown arrow next to estado.
  • Select Conditional formatting, then select Background color.
  • In the dialog that opens, set Format style to Rules.
  • Change the What field should we base this on? dropdown to estado, and change Summarization to First.
  • Add three rules: if value contains en-riesgo set background to red, if value contains retrasado set background to yellow, and if value contains en-tiempo set background to green.
  • Click OK to apply the formatting.

What does conditional formatting do here?

Each row in the AI Insights table now has a colored background matching its risk level. Red draws immediate attention to at-risk projects. Green confirms healthy projects at a glance.

Finally, add a Slicer so executives can filter the AI insights to a specific project.

  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Slicer visual icon.
  • Drag proyecto from df_projects into the slicer's Field well.
  • Position the slicer to the left of the AI Insights table.
  • Test the interaction by clicking a project name in the slicer. The AI Insights table should filter to show only that project's row.
  • Save your report by pressing Ctrl+S. Name the file something descriptive like dashboard_construccion_ai.pbix.

Your dashboard now has all the required visuals working together. Selecting a project in the slicer updates the AI Insights panel and highlights the corresponding bars in your charts.

Slicer not filtering the AI Insights table?

Make sure the relationship between df_projects and df_ai is active. Go to Model view (the icon on the left sidebar) and confirm the line connecting the two tables by proyecto is solid, not dashed.

Help me troubleshoot my slicer cross-filter issue

Your executive dashboard is complete. It combines traditional KPI metrics with AI-generated narrative insights, all in one interactive report. Next up, the Secret Mission takes your solution further by making the AI provider swappable.

Secret mission

Multi-Provider - Add NVIDIA NIM as Alternative

Your dashboard works with DeepSeek, but what happens if their API goes down? Real production systems never depend on a single provider. In this secret mission, you will refactor your script to support multiple AI providers and add NVIDIA NIM as a second backend. One config change switches the entire pipeline between providers.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs. API charges only occur when you manually refresh data in Power BI Desktop (under $0.05 per refresh from your DeepSeek balance).

Resources you used:

  • Power BI Desktop file (.pbix) stored on your local machine.
  • Python script (construccion_ai.py) stored on your local machine.
  • Power BI Desktop application installed on Windows.
  • DeepSeek API key (active until revoked).
  • NVIDIA NIM API key (Secret Mission only, active until revoked).

Keep everything running

No action needed. Choose this if you are still actively building or want to revisit the dashboard later.

  • Your .pbix file works offline. It only calls the API when you click Refresh in Power BI Desktop.
  • Your construccion_ai.py script remains on disk and can be reused anytime.
  • DeepSeek charges only when you trigger a data refresh (under $0.05 per run).
  • NVIDIA NIM is free and rate-limited. No charges accrue while idle.

Pause - I'll come back to this later

No resources to pause. Both APIs are pay-per-use or free-tier and do not incur charges unless you trigger a data refresh in Power BI Desktop.

  • Close Power BI Desktop to free up system memory.
  • Your .pbix file and construccion_ai.py remain safely on disk for when you return.
  • Your API keys remain active but cost nothing while idle.

Delete - I don't want to use this again

Remove all project resources and revoke API access to start fresh.

  • Delete your .pbix file. Right-click the file in File Explorer and select Delete.
  • Delete construccion_ai.py. Right-click the file in File Explorer and select Delete.
  • Uninstall Power BI Desktop. Open Settings, then select Apps, then Installed apps. Search for Power BI Desktop, click the three-dot menu, and select Uninstall.
  • Revoke your DeepSeek API key. Log in to the DeepSeek Platform where you originally created the key, locate the key in your API keys list, and delete it from there.
  • Revoke your NVIDIA NIM API key (Secret Mission only). Go to build.nvidia.com, open your profile, then select Settings and API keys. Delete the key you created for this project.
  • Optionally uninstall the Python packages by running the command below. Only do this if you do not use pandas or requests in other Python projects.
pip uninstall pandas requests -y

Nice Work!

Nice Work!

You just built a production-grade Power BI dashboard that integrates AI-powered analysis directly into a business intelligence workflow. This is the exact intersection of BI and AI that modern construction firms need.

You've learned how to:

  • Build a Power BI dashboard with realistic construction project KPIs imported via Python scripting.
  • Integrate an AI language model (DeepSeek V4 Flash) into Power BI to generate executive-level narrative summaries alongside traditional visualizations.
  • Create a vendor-agnostic API architecture using pandas DataFrames and the OpenAI-compatible format so your dashboard can call any compatible provider.
  • Design a multi-provider config pattern that swaps between DeepSeek and NVIDIA NIM with a single variable change (Secret Mission).

Ready to quiz yourself?