---

name: PowerBI_Analysis_Automator description: Automates the process of preparing data for Power BI analysis. It cleans data using Pandas, generates DAX measures, and creates Python integration scripts.

PowerBI Analysis Automator Skill

This skill assists in transforming raw CSV data into a Power BI-ready state. It performs data cleaning, generates DAX measures, and provides the necessary Python scripts for integration.

Usage

Input: A path to a CSV file (e.g., data/sales_data.csv). Context: Optional details about the data schema or specific analysis goals.

Instructions

Follow these steps to process the request:

1. Analyze the Request: Identify the CSV file path and any specific analysis requirements (e.g., specific columns to target).
2. Generate Data Cleaning Code (Mental Check or Scratchpad):
   • Formulate Pandas code to load the CSV.
   • Remove duplicate rows.
   • Handle missing values (e.g., fill with 0 for numeric, 'Unknown' for categorical, or drop if appropriate).
3. Generate DAX Measure:
   • Create a standard DAX measure for "Total Sales" or relevant aggregation based on column names (e.g., Sales, Revenue, Amount).
   • Format: Total Sales = SUM('Table'[ColumnName])
4. Generate Power BI Python Script:
   • Create a Python script suitable for a Power BI "Python script visual" or Power Query transformation.
   • The script should:
     • Import pandas and matplotlib.pyplot.
     • Load the dataset (assuming Power BI passes it as dataset in a visual, or loading from source in Power Query). Note: For the output script, prefer the pattern where the user can copy-paste into Power BI's editor.
     • Include a basic plot code snippet (e.g., bar chart).
5. Output Format:
   • Present the DAX Measure.
   • Present the Python Script.
   • Explain briefly what the script does.

Few-Shot Examples

Example 1: Sales Data Analysis

User Input: "Analyze sales_january.csv. It has columns: Product, Quantity, UnitPrice, Region."

Response:

Here is the analysis setup for sales_january.csv.

1. Data Cleaning (Pandas Logic Applied)

• Duplicates removed.
• Missing Quantity or UnitPrice filled with 0.
• Calculated TotalAmount = Quantity * UnitPrice.

2. DAX Measure Use this measure in Power BI to calculate total sales:

Total Sales = SUM('sales_january'[TotalAmount])

3. Python Script for Power BI Copy this into a Power BI Python Visual to visualize Sales by Region:

# 'dataset' holds the input data for this script
import matplotlib.pyplot as plt
import pandas as pd

# Standard Power BI dataframe loading (if testing locally, uncomment the defined dataframe)
# dataset = pd.read_csv('sales_january.csv')

# Data Cleaning
dataset.drop_duplicates(inplace=True)
dataset['Quantity'] = dataset['Quantity'].fillna(0)
dataset['UnitPrice'] = dataset['UnitPrice'].fillna(0)
dataset['TotalAmount'] = dataset['Quantity'] * dataset['UnitPrice']

# Aggregation
data_agg = dataset.groupby('Region')['TotalAmount'].sum()

# Plotting
plt.figure(figsize=(10, 6))
data_agg.plot(kind='bar', color='skyblue')
plt.title('Total Sales by Region')
plt.xlabel('Region')
plt.ylabel('Sales Amount')
plt.tight_layout()
plt.show()

Example 2: Inventory Check

User Input: "Check inventory.csv. Columns: ItemID, Category, StockLevel."

Response:

1. Data Cleaning

• Duplicates removed based on ItemID.
• Missing StockLevel rows dropped to avoid skewing data.

2. DAX Measure

Total Stock = SUM('inventory'[StockLevel])

3. Python Script for Power BI Use this to visualize stock distribution:

import matplotlib.pyplot as plt
import pandas as pd

# Clean missing values
dataset.dropna(subset=['StockLevel'], inplace=True)
dataset.drop_duplicates(subset=['ItemID'], inplace=True)

# Plot
plt.figure(figsize=(8, 8))
dataset['Category'].value_counts().plot(kind='pie', autopct='%1.1f%%')
plt.title('Inventory Distribution by Category')
plt.show()