Excel Power Query Templates
50+ Power Query M code templates for data transformation, API connections, consolidation, and automated reporting workflows.
Product Preview
Try the interactive reader and demo tools below, or get the full product with all content unlocked.
Interactive Reader (Free Preview) ⚙ Try Demo Tools Download Free SampleFile Structure 24 files
Documentation Preview README excerpt
Excel Power Query M Code Templates
50+ ready-to-use Power Query (M language) templates for data transformation, API connections, file consolidation, and automated reporting workflows. Each template includes the complete M code plus usage instructions.
What's Included
- Data Transformation (10 templates) — Unpivot, pivot, split, merge, conditional columns
- API & Web Connections (8 templates) — REST APIs, pagination, OAuth, web scraping
- File Consolidation (7 templates) — Combine CSVs, Excel files, folder contents
- Pivot & Unpivot (6 templates) — Reshape data between wide and long formats
- Date Tables (5 templates) — Calendar generation, fiscal years, holidays
- Fuzzy Matching (4 templates) — Approximate joins, deduplication
- Reporting Workflows (6 templates) — Incremental refresh, parameterized queries
- Data Cleaning (7 templates) — Trim, standardize, validate, error handling
- Advanced Patterns (5 templates) — Recursion, custom functions, buffering
How to Use
1. Open Power Query Editor (Data → Get Data → Launch Power Query Editor)
2. Create a new Blank Query (Home → New Source → Blank Query)
3. Open Advanced Editor (View → Advanced Editor)
4. Paste the M code from the template
5. Modify parameters (source paths, URLs, column names) to match your data
6. Click "Done" and verify the preview
File Format
Each .pq file contains M code. The first comment block explains the template:
// ═══════════════════════════════════════════
// Template: [Name]
// Category: [Category]
// Purpose: [What it does]
// Parameters to modify: [List of things to customize]
// ═══════════════════════════════════════════
let
// ... M code ...
in
Result
Requirements
- Excel 2016+ or Excel 365 (with Power Query)
- Or Power BI Desktop (uses the same M engine)
- Some templates require network access (API templates)
License
MIT License — See LICENSE file.
Part of [Data Analyst Toolkit](https://datanest-stores.com/data-analyst/)
Content Sample guides/common-errors.md
Power Query M — Common Errors & Fixes
A troubleshooting reference for the errors you'll encounter most often when
adapting these templates to your own data.
Error: "Expression.Error: The column 'X' of the table wasn't found."
Cause: You referenced a column name that doesn't exist in your data.
Common triggers:
- Source renamed a column (e.g., "Revenue" → "Total Revenue")
- Typo in column name (case-sensitive!)
- Column was removed in an earlier step
Fix:
// Check actual column names first:
Table.ColumnNames(YourTable)
// Use try...otherwise for optional columns:
try YourTable[MaybeColumn] otherwise null
Error: "DataFormat.Error: We couldn't convert to Number."
Cause: A cell that should be numeric contains text (e.g., "N/A", "-", "TBD").
Fix:
// Replace non-numeric sentinels BEFORE type conversion:
Cleaned = Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Amount"}),
Typed = Table.TransformColumnTypes(Cleaned, {{"Amount", type number}})
// Or use try per-row:
Table.AddColumn(Source, "SafeAmount", each
try Number.From([RawAmount]) otherwise null, type nullable number)
Error: "Formula.Firewall: Query references other queries or steps..."
Cause: Power Query's privacy firewall is blocking a cross-source operation
(e.g., using a parameter from one source to filter another).
... and much more in the full download.