Power Query at Scale: Loading AtlasParts into Power BI | Excel & Power BI S2 Ep2

📂 Download the AtlasParts dataset and the Episode 8 starter file: https://github.com/GoCelesteAI/excel-powerbi-for-finance Episode 2 of Season 2 of *Excel & Power BI for Finance* — Power Query at scale. The bridge in Episode 1 named the three engines under the hood; this episode zooms into the first of those three. Power Query in Power BI Desktop is the same M language you already used in Season 1 Episode 5 to clean a customer master. Same Advanced Editor. Same Applied Steps. What's different is what it feeds: not a worksheet table, but a model — eight Tables loaded at once, refresh-clean, parametrised, ready for the star schema in Episode 3. We use the AtlasParts dataset throughout — eight CSV files representing a real ERP extract — and walk through Get Data → Folder, the Combine vs Add-as-New-Query decision, Merge for joining shapes (customers ⨝ countries → adds a region column), Append for stacking same-shape files (twelve monthly GL files → one yearly Table), Parameters for portability (one place to set the file path so refresh works on any machine), and three patterns for handling messy CSVs at the Power Query layer instead of pushing the mess into your visuals. What You'll Learn: - Why Power Query in Power BI is the same engine as Power Query in Excel — the skill transfers one-for-one, only the load target changes (Vertipaq model, not a worksheet) - Get Data → Folder — Power BI's most powerful loading pattern: point at AtlasParts/, pick up all 8 CSVs at once, and choose between Combine (same-shape stacking) or Add as New Query (one Table per file) - Merge vs Append — Merge is SQL JOIN (customers + countries → region attached); Append is SQL UNION ALL (twelve monthly GL files → one yearly Table). Knowing which to reach for is half the Power Query craft. - Parameters for portable refresh — Manage Parameters → New Parameter → AtlasFolder. Replace hard-coded paths in every Source step. Now eight queries pick up a single named value instead of breaking when the file moves. - Three error-handling patterns — Replace Errors (right-click), Conditional Columns (rule-based cleanup), and try / otherwise (M's try-catch for any expression that might fail). Push cleanup upstream so visuals never see error tokens. - Close & Apply — Power BI writes every step to the .pbix and lands the Tables in Vertipaq. AtlasParts' 4.1M-row GL compresses from 220MB (.xlsx) to 38MB (Vertipaq) — about 6× compression, queryable in milliseconds. Timestamps: 0:00 - Intro — Power Query at scale 0:24 - What's in this episode 0:52 - The Power Query you already know — three jobs 1:35 - Get Data → Folder — the AtlasParts pattern 2:27 - Merge vs Append — joining shapes vs stacking files 3:29 - Parameters — making the file path portable 4:19 - Error handling — three patterns 5:19 - Close & Apply — the model after 6:11 - Up next — Episode 3 connects the Tables (star schema) 6:48 - Recap — three takeaways 7:23 - Up next — Star schema for finance Key Takeaways: 1. Power Query in Power BI is the same M language from Season 1 Episode 5 — you already know it. The Advanced Editor, the Applied Steps panel, every transformation function. What changes is that the load target is a Vertipaq model that holds many Tables, not a single worksheet. 2. Four Power Query habits that scale past a single file: Get Data → Folder loads many files at once; Merge joins shapes (SQL JOIN); Append stacks same-shape files (SQL UNION ALL); Parameters give you one named place to change a path or environment. 3. Push cleanup upstream. Replace Errors, Conditional Columns, and try/otherwise are the three patterns. By the time data hits the model, it should be clean — visuals and DAX measures should never have to defend against an error token. 4. Vertipaq's compression is the headline number — AtlasParts' 4.1M GL rows go from 220MB in Excel to 38MB in Power BI (~6×). The qualitative shift matters more: the model is loaded once and queried many times. Refresh re-runs every query from Source onward. 5. Episode 8 is deliberately the easy one. The hard parts of Power BI — star schema design, DAX with row context and filter context, time intelligence — are still ahead. By starting with the engine you already know, we get AtlasParts loaded without learning a new skill. #PowerBI #PowerQuery #ExcelToPowerBI #PowerBIDesktop #FinanceAnalytics #DataModeling #BusinessIntelligence #FinancialReporting #ETL #ExcelForFinance --- Generated by GoCelesteAI · part of the Excel & Power BI for Finance series