# =============================================================================
# Power Query & Data Model End-to-End Test
# =============================================================================
#
# Tests: Complete BI workflow - Import → Model → Analyze → Visualize
#
# This is a SINGLE SESSION with multiple tests that build on each other.
# Each test continues from where the previous one left off.
# The LLM remembers the file and context from previous tests.
#
# Workflow:
# Test 1: Create file and set up data
# Test 2: Add to Data Model and create measures
# Test 3: Create PivotTable from Data Model
# Test 4: Add chart visualization
# Test 5: Final analysis and close
#
# Prerequisites:
# - Windows 10/11 with Excel installed
# - Excel MCP Server
# - Environment: AZURE_OPENAI_ENDPOINT
#
# =============================================================================
criteria:
success_rate: 1 # 100% required
providers:
- name: azure-openai-gpt41
type: AZURE
auth_type: entra_id
model: gpt-4.1
baseUrl: "{{AZURE_OPENAI_ENDPOINT}}"
version: 2025-01-01-preview
retry:
retry_on_429: true
max_retries: 5
- name: azure-openai-gpt52-chat
type: AZURE
auth_type: entra_id
model: gpt-5.2-chat
baseUrl: "{{AZURE_OPENAI_ENDPOINT}}"
version: 2025-01-01-preview
retry:
retry_on_429: true
max_retries: 5
# Provider for clarification detection judge (using gpt-4.1 for better accuracy)
- name: azure-openai-judge
type: AZURE
auth_type: entra_id
model: gpt-4.1
baseUrl: "{{AZURE_OPENAI_ENDPOINT}}"
version: 2025-01-01-preview
servers:
- name: excel-mcp
type: stdio
command: "{{SERVER_COMMAND}}"
server_delay: 30s
agents:
- name: gpt41-agent
servers:
- name: excel-mcp
provider: azure-openai-gpt41
system_prompt: |
You are {{AGENT_NAME}}, an Excel automation agent using {{PROVIDER_NAME}}.
RULES:
- Execute tasks immediately without asking for confirmation
- Never ask clarifying questions - make reasonable assumptions and proceed
- Never show Excel to the user - keep it hidden
- Format Excel files professionally (proper column widths, headers, number formats, cell styles)
- Always format data ranges as Excel Tables (not plain ranges)
- Report results after completion
clarification_detection:
enabled: true
judge_provider: azure-openai-judge
- name: gpt52-chat-agent
servers:
- name: excel-mcp
provider: azure-openai-gpt52-chat
system_prompt: |
You are {{AGENT_NAME}}, an Excel automation agent using {{PROVIDER_NAME}}.
RULES:
- Execute tasks immediately without asking for confirmation
- Never ask clarifying questions - make reasonable assumptions and proceed
- Never show Excel to the user - keep it hidden
- Format Excel files professionally (proper column widths, headers, number formats, cell styles)
- Always format data ranges as Excel Tables (not plain ranges)
- Report results after completion
clarification_detection:
enabled: true
judge_provider: azure-openai-judge
settings:
verbose: true
max_iterations: 20
sessions:
# ==========================================================================
# Complete BI Dashboard Build - Progressive workflow
# Each test builds on the previous one within the same session
# ==========================================================================
- name: "Sales Dashboard Build"
tests:
# ----------------------------------------------------------------------
# Step 1: Create the workbook and enter raw data
# ----------------------------------------------------------------------
- name: "Create workbook with sales data"
prompt: |
I want to build a sales analysis dashboard. Let's start by creating a new workbook.
Create a new Excel file at {{TEST_RESULTS_PATH}}/sales-dashboard-{{randomValue type='UUID'}}.xlsx
Then enter this quarterly sales data starting at A1:
Region, Quarter, Product, Revenue, Units
North, Q1, Laptops, 45000, 30
North, Q1, Phones, 28000, 70
North, Q2, Laptops, 52000, 35
North, Q2, Phones, 31000, 80
South, Q1, Laptops, 38000, 25
South, Q1, Phones, 24000, 60
South, Q2, Laptops, 48000, 32
South, Q2, Phones, 29000, 75
Convert this data into an Excel table called "Sales"
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(sales|table|created|success)"
# ----------------------------------------------------------------------
# Step 2: Add to Data Model and create measures
# ----------------------------------------------------------------------
- name: "Add to Data Model with measures"
prompt: |
Great! Now I want to analyze this data using the Data Model.
Add the Sales table to the Data Model.
Then create these measures:
- Total Revenue - sum of all revenue
- Total Units - sum of all units sold
- Revenue Per Unit - average revenue per unit
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(measure|revenue|units|data model|created)"
# ----------------------------------------------------------------------
# Step 3: Create PivotTable from Data Model
# ----------------------------------------------------------------------
- name: "Create PivotTable analysis"
prompt: |
Now let's analyze the data. Create a PivotTable on a new sheet using the Data Model.
Set it up to show:
- Regions as rows
- Quarters as columns
- Total Revenue as the values
Which region performed better in Q2?
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(pivot|north|south|q2|revenue)"
# ----------------------------------------------------------------------
# Step 4: Add chart visualization
# ----------------------------------------------------------------------
- name: "Add chart for visualization"
prompt: |
Perfect! Now add a column chart based on the PivotTable to visualize the revenue by region and quarter.
Give the chart a title like "Quarterly Revenue by Region"
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(chart|column|revenue|created)"
# ----------------------------------------------------------------------
# Step 5: Save and summarize
# ----------------------------------------------------------------------
- name: "Save and close"
prompt: |
Save and close the file.
Give me a summary: how many measures did we create, and which region had better Q2 revenue?
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(measure|north|south|saved|closed)"
# ==========================================================================
# Multi-Table Star Schema - Related tables workflow
# ==========================================================================
- name: "Star Schema Analysis"
tests:
# ----------------------------------------------------------------------
# Step 1: Create Products dimension table
# ----------------------------------------------------------------------
- name: "Create Products dimension"
prompt: |
I need to set up a proper star schema for analysis.
Create a new Excel file at {{TEST_RESULTS_PATH}}/star-schema-{{randomValue type='UUID'}}.xlsx
On a sheet called "Products", enter this product catalog starting at A1:
ProductID, ProductName, Category, UnitPrice
P001, Laptop Pro, Electronics, 1200
P002, Wireless Headphones, Electronics, 150
P003, Standing Desk, Furniture, 400
P004, Ergonomic Chair, Furniture, 250
P005, USB Hub, Electronics, 35
Make it a table called "Products" and add it to the Data Model.
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(products|table|data model|success)"
# ----------------------------------------------------------------------
# Step 2: Create Orders fact table
# ----------------------------------------------------------------------
- name: "Create Orders fact table"
prompt: |
Now let's add the transaction data.
Create a new sheet called "Orders" with this data starting at A1:
OrderID, ProductID, Quantity, OrderDate
1001, P001, 2, 2024-03-01
1002, P002, 5, 2024-03-02
1003, P003, 1, 2024-03-03
1004, P001, 1, 2024-03-04
1005, P004, 3, 2024-03-05
1006, P002, 4, 2024-03-06
1007, P005, 10, 2024-03-07
1008, P001, 2, 2024-03-08
Make it a table called "Orders" and add it to the Data Model.
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(orders|table|data model|success)"
# ----------------------------------------------------------------------
# Step 3: Create relationship between tables
# ----------------------------------------------------------------------
- name: "Create table relationship"
prompt: |
Now link the tables together.
Create a relationship between the Orders and Products tables using ProductID.
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(relationship|productid|created|success)"
# ----------------------------------------------------------------------
# Step 4: Analyze across related tables with PivotTable
# ----------------------------------------------------------------------
- name: "Cross-table PivotTable analysis"
prompt: |
Now for the analysis! Create a PivotTable on a new sheet that shows:
- Product Categories as rows (from the Products table)
- Sum of Quantity as the values (from the Orders table)
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(pivot|electronics|furniture|category)"
# ----------------------------------------------------------------------
# Step 5: Add chart and close
# ----------------------------------------------------------------------
- name: "Add pie chart and close"
prompt: |
Add a pie chart showing the quantity distribution by category.
Save and close the file.
Which category had more orders - Electronics or Furniture?
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: output_regex
pattern: "(?i)(pie|chart|saved|closed|success)"
# ==========================================================================
# Power Query Import - Import Amazon Sales data (1400+ products)
# ==========================================================================
- name: "Power Query Amazon Sales Analysis"
tests:
# ----------------------------------------------------------------------
# Step 1: Import Amazon product data using Power Query
# ----------------------------------------------------------------------
- name: "Import Amazon CSV with Power Query"
prompt: |
I want to analyze Amazon product sales data.
Create a new Excel file at {{TEST_RESULTS_PATH}}/amazon-analysis-{{randomValue type='UUID'}}.xlsx
Use Power Query to import this CSV file:
{{TEST_DIR}}/../Fixtures/amazon.csv
Name the query "Products"
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: tool_called
tool: excel_powerquery
- type: output_regex
pattern: "(?i)(power.?query|import|products|success|created)"
# ----------------------------------------------------------------------
# Step 2: Add to Data Model for analysis
# ----------------------------------------------------------------------
- name: "Build star schema from flat data"
prompt: |
The Products data from the Power Query is on a worksheet as an Excel Table, but I need it in Power Pivot (the Data Model) for DAX analysis.
Add the Products table to the Data Model so I can create DAX measures on it.
After adding to the Data Model, analyze the data structure:
- Which columns are dimensions (descriptive attributes for slicing/filtering)?
- Which columns are facts/measures (numeric values to aggregate)?
Confirm the table is now in the Data Model and ready for DAX measures.
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: tool_called
tool: excel_table
- type: output_regex
pattern: "(?i)(dimension|fact|data.?model|added)"
# ----------------------------------------------------------------------
# Step 3: Add measures to the star schema
# ----------------------------------------------------------------------
- name: "Add to Data Model with rating measures"
prompt: |
Now create some useful DAX measures on your fact table:
1. Average Rating
2. Total Products
3. Average Discount Percentage
4. Total Potential Revenue (sum of original prices)
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: tool_called
tool: excel_datamodel
- type: output_regex
pattern: "(?i)(measure|rating|discount|revenue|created)"
# ----------------------------------------------------------------------
# Step 4: Create PivotTable using the star schema
# ----------------------------------------------------------------------
- name: "Analyze products by category"
prompt: |
Create a PivotTable on a new sheet using your star schema.
Show product categories as rows with Total Products and Average Rating as values.
Which category has the most products and what's their average rating?
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: tool_called
tool: excel_pivottable
- type: output_regex
pattern: "(?i)(pivot|category|rating|products)"
# ----------------------------------------------------------------------
# Step 5: Add chart and save
# ----------------------------------------------------------------------
- name: "Add chart and close"
prompt: |
Add a bar chart showing:
- Categories on the X-axis
- Total Products and Average Rating as values
Save and close the file.
Summarize the star schema you built: how many dimension tables, fact tables, relationships, and measures did you create?
assertions:
- type: no_hallucinated_tools
- type: no_clarification_questions
- type: tool_called
tool: excel_file
- type: output_regex
pattern: "(?i)(chart|star.?schema|dimension|fact|relationship|measure|saved|closed)"
