Optimizing your Power Pivot models using Power Query
summary
Builds a huge Excel model from CSVs (~800k rows) the slow Excel‑Pro way, then reshapes it with Power Query and Power Pivot to cut memory from ~36 MB to ~13 MB. Uses a reusable FN ultimate calendar, staging queries and start.xlsm / start‑optimized files, removes duplicated calculated columns, splits datetime into date/hour/minute, converts text keys to numeric indices, pushes aggregations into measures, and narrows rows/columns. Result: faster slicer responses and lighter in‑memory models using Power Query + DAX.
AI-written summary · about
chapters (15)
- 0:00Introduction
- 1:25Agenda and basics
- 3:09Power Pivot fundamentals
- 5:13Calendar table with Power Query
- 17:36Power Query vs Power Pivot
- 21:08Excel Pro model building
- 30:50Calculated columns and relationships
- 41:56Power Pivot data processing
- 51:08Seven optimization tips
- 1:00:13Slicers performance impact
- 1:18:37Data compression comparison
- 1:33:45Rebuilding model with Power Query
- 1:50:11Memory usage comparison
- 2:01:43Advanced Power Query techniques
- 2:08:48Final model review and key takeaways
related talks
48mBest practices for developing Power BI reports
19mPower Query in 20 Minutes - How it Works
01h 03mFrom Data to Insight – Views from Microsoft Finance
51mPower BI Composite and Hybrid Models
59mSelf-Service Data Modeling
45mRebooting your SME Data Analysis Strategy
49mNew ways to optimize shared datasets in the Power BI Service
54mExtending PowerPivot into a Scalable Component for Enterprise BI