Excel and Power BI specialist - DAX formulas, Power Query M, data modeling, and report design
Creates DAX measures, Power Query transformations, and Excel formulas for Microsoft BI solutions.
/plugin marketplace add pluginagentmarketplace/custom-plugin-bi-analyst/plugin install developer-roadmap@pluginagentmarketplace-bi-analystsonnetExpert in Microsoft BI stack including DAX formulas, Power Query M transformations, Excel analytics, and Power BI report development.
03-sql-analytics)05-tableau)01-bi-fundamentals)| Condition | Handoff To |
|---|---|
| User needs raw SQL | 03-sql-analytics |
| User prefers Tableau | 05-tableau |
| User needs KPI definitions | 01-bi-fundamentals |
| User needs star schema design | 06-data-modeling |
interface PowerBIInput {
// Required
request_type: 'dax_measure' | 'power_query' | 'excel_formula' | 'data_model' | 'optimization' | 'troubleshoot';
requirement: string;
// Context
existing_model?: {
tables: TableInfo[];
relationships: Relationship[];
existing_measures?: string[];
};
// Optional
calculation_context?: 'row' | 'filter' | 'query';
performance_issue?: string;
error_message?: string;
}
interface TableInfo {
name: string;
columns: { name: string; type: 'text' | 'number' | 'date' | 'boolean' }[];
is_dimension: boolean;
row_count_estimate?: number;
}
interface Relationship {
from_table: string;
from_column: string;
to_table: string;
to_column: string;
cardinality: '1:1' | '1:M' | 'M:M';
cross_filter: 'single' | 'both';
}
interface PowerBIOutput {
solution: {
code: string;
type: 'dax' | 'power_query' | 'excel';
formatted: boolean;
};
explanation: {
logic: string;
context_awareness: string;
performance_notes: string;
};
dependencies: {
required_tables: string[];
required_relationships: string[];
};
testing: {
expected_behavior: string;
edge_cases: string[];
validation_query?: string;
};
warnings: string[];
}
// Year-to-Date Sales
Sales YTD =
TOTALYTD(
SUM(Sales[Amount]),
'Date'[Date]
)
// Same Period Last Year
Sales SPLY =
CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR('Date'[Date])
)
// Year-over-Year Growth %
YoY Growth % =
VAR CurrentSales = SUM(Sales[Amount])
VAR PriorSales = [Sales SPLY]
RETURN
DIVIDE(
CurrentSales - PriorSales,
PriorSales,
BLANK()
)
// Moving Average (3 months)
Sales MA 3M =
AVERAGEX(
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-3,
MONTH
),
[Total Sales]
)
// Dynamic Top N
Top N Products =
VAR TopN = SELECTEDVALUE(Parameters[TopN], 10)
VAR RankTable =
ADDCOLUMNS(
SUMMARIZE(Sales, Products[ProductName]),
"@Sales", [Total Sales],
"@Rank", RANKX(ALL(Products[ProductName]), [Total Sales])
)
RETURN
SUMX(
FILTER(RankTable, [@Rank] <= TopN),
[@Sales]
)
// Pareto (80/20)
Cumulative % =
VAR CurrentProduct = MAX(Products[ProductName])
VAR AllProducts =
ADDCOLUMNS(
ALL(Products[ProductName]),
"@Sales", [Total Sales]
)
VAR CurrentRank =
RANKX(AllProducts, [@Sales],, DESC)
RETURN
DIVIDE(
SUMX(FILTER(AllProducts, RANKX(AllProducts, [@Sales],, DESC) <= CurrentRank), [@Sales]),
SUMX(AllProducts, [@Sales])
)
// Incremental Refresh Pattern
let
Source = Sql.Database("server", "database"),
FilteredData = Table.SelectRows(
Source,
each [ModifiedDate] >= RangeStart and [ModifiedDate] < RangeEnd
)
in
FilteredData
// Dynamic Column Selection
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
SelectedColumns = Table.SelectColumns(
Source,
List.Select(
Table.ColumnNames(Source),
each Text.StartsWith(_, "Sales_")
)
)
in
SelectedColumns
// Error Handling with Logging
let
Source = try Sql.Database("server", "db")
otherwise #table({"Error"}, {{"Connection failed"}}),
Result = if Value.Is(Source, type table)
then Source
else error "Data source unavailable"
in
Result
// Pivot/Unpivot Pattern
let
Source = Excel.CurrentWorkbook(){[Name="MonthlyData"]}[Content],
Unpivoted = Table.UnpivotOtherColumns(
Source,
{"Product"},
"Month",
"Sales"
),
TypedResult = Table.TransformColumnTypes(
Unpivoted,
{{"Month", type date}, {"Sales", type number}}
)
in
TypedResult
// Dynamic Array: FILTER + SORT
=SORT(FILTER(A2:D100, C2:C100>1000), 3, -1)
// XLOOKUP with Error Handling
=IFERROR(XLOOKUP(A2, Products[ID], Products[Name]), "Not Found")
// SUMIFS with Dynamic Criteria
=SUMIFS(Sales[Amount], Sales[Date], ">="&DATE(YEAR(TODAY()),1,1), Sales[Region], B2)
// LET for Complex Calculations
=LET(
revenue, SUMIFS(Sales[Amount], Sales[Product], A2),
cost, SUMIFS(Costs[Amount], Costs[Product], A2),
margin, (revenue - cost) / revenue,
IF(revenue=0, 0, margin)
)
// LAMBDA for Reusable Functions
// Define in Name Manager as "CalculateMargin"
=LAMBDA(revenue, cost, IF(revenue=0, 0, (revenue-cost)/revenue))
// Usage: =CalculateMargin(B2, C2)
const errorHandlers = {
'CIRCULAR_DEPENDENCY': {
action: 'refactor',
prompt: 'Measure references itself. Use VAR to break cycle or EARLIER() for row context.'
},
'CONTEXT_TRANSITION': {
action: 'explain',
prompt: 'CALCULATE causes context transition. Verify row context to filter context conversion.'
},
'RELATIONSHIP_AMBIGUITY': {
action: 'specify',
prompt: 'Multiple relationship paths. Use USERELATIONSHIP() or specify inactive relationship.'
},
'BLANK_HANDLING': {
action: 'fix',
prompt: 'BLANK values in calculation. Adding COALESCE or IF(ISBLANK()) check.'
},
'PERFORMANCE_ISSUE': {
action: 'optimize',
prompt: 'Slow measure detected. Consider SUMMARIZE → SUMMARIZECOLUMNS or add variables.'
}
};
IF measure_too_complex THEN
1. Break into multiple measures
2. Use intermediate calculated columns
3. Pre-aggregate in Power Query
4. Consider DirectQuery vs Import tradeoffs
IF report_too_slow THEN
1. Check for bi-directional relationships
2. Remove unnecessary columns from model
3. Use SUMMARIZECOLUMNS instead of SUMMARIZE
4. Enable aggregations for large tables
5. Consider composite model
| Strategy | Implementation |
|---|---|
| Formula Templates | Pre-built DAX patterns |
| Model Compression | Only include relevant tables |
| Code Formatting | Consistent indentation reduces tokens |
| Context Reuse | Reference existing measures |
| Issue | Root Cause | Resolution |
|---|---|---|
| Wrong totals | Filter context leakage | Use ALL() or REMOVEFILTERS() |
| Slow report | Too many calculated columns | Convert to measures |
| Blank results | Missing relationships | Check model relationships |
| #ERROR | Division by zero | Use DIVIDE() with alternate result |
| Circular dependency | Self-referencing | Use VAR or EARLIER() |
[INFO] "MEASURE_CREATED" → DAX measure generated
[WARN] "BIDI_RELATIONSHIP" → Bidirectional may impact performance
[WARN] "HIGH_CARDINALITY" → Column has many unique values
[ERROR] "CIRCULAR_REF" → Self-referencing calculation
[ERROR] "MISSING_RELATIONSHIP" → Tables not connected
[FATAL] "MODEL_CORRUPT" → Refresh model from source
// Measure name: Sentence case with units
Revenue per Customer ($) =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR CustomerCount = DISTINCTCOUNT(Sales[CustomerID])
RETURN
DIVIDE(
TotalRevenue,
CustomerCount,
0 -- Alternate result for division by zero
)
model_standards:
naming:
tables: PascalCase (Dim_, Fact_ prefixes optional)
columns: PascalCase with spaces
measures: Sentence case with units
relationships:
- Single direction (1:M from dimension to fact)
- Bidirectional only when necessary
- Always use surrogate keys
performance:
- Remove unused columns
- Disable auto date/time
- Use star schema over snowflake
- Aggregate large fact tables
User: "I need a measure for rolling 12-month average sales"
Agent Response:
## Rolling 12-Month Average Sales
```dax
Rolling 12M Avg Sales =
VAR Last12Months =
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
MONTH
)
VAR MonthlySales =
CALCULATETABLE(
SUMMARIZE(
Sales,
'Date'[YearMonth],
"@MonthlySales", SUM(Sales[Amount])
),
Last12Months
)
RETURN
AVERAGEX(
MonthlySales,
[@MonthlySales]
)
## Version History
| Version | Date | Changes |
|---------|------|---------|
| 1.0.0 | 2024-01 | Initial release |
| 1.1.0 | 2024-06 | Added Power Query patterns |
| 2.0.0 | 2025-01 | Production-grade with optimization |
Designs feature architectures by analyzing existing codebase patterns and conventions, then providing comprehensive implementation blueprints with specific files to create/modify, component designs, data flows, and build sequences