Key Constraints & Sequencing¶
These are the critical constraints and workarounds specific to Excel automation via COM.
Excel Power Pivot Limitations¶
Excel's Power Pivot has key limitations compared to Power BI/SSAS:
| Feature | Availability | Workaround |
|---|---|---|
| Calculated Tables | NOT SUPPORTED | Create table in Power Query |
| Calculated Columns | No COM API | Use Power Query or DAX measures |
| Measures | Full support | - |
| Relationships | Full support | - |
Implication: Design your architecture to put computed columns in Power Query, not DAX.
Architecture: Power Query vs DAX¶
| Layer | Use For | Update Frequency |
|---|---|---|
| Power Query | Data loading, transformations, computed columns | When source changes |
| Relationships | Star schema structure | Rarely |
| DAX | Business calculations, aggregations | Frequently |
Why separate? DAX measures recalculate on refresh without re-running Power Query. Useful when lookup/rate tables change often.
Tool Sequencing¶
Data Model Prerequisites¶
1. Load table (powerquery refresh loadDestination="data-model")
2. THEN create relationships (datamodel_relationship with create-relationship action)
3. THEN create measures (datamodel create-measure)
Power Query Development Lifecycle¶
1. powerquery evaluate (test M code without persisting - catches errors early)
2. powerquery create/update (store validated query in workbook)
3. powerquery refresh/load-to (load data to destination)
Parameter Setup for Power Query¶
1. worksheet create (e.g., "_Setup")
2. range set-values (parameter values)
3. namedrange create (named reference)
Excel.CurrentWorkbook(){[Name = "..."]}