← Back to all products

Excel Power Query Templates

$29

50+ Power Query M code templates for data transformation, API connections, consolidation, and automated reporting workflows.

24 files
MarkdownJSONExcelPower BI

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 Sample

File Structure 24 files

excel-power-query-templates/ ├── INDEX.md ├── LICENSE ├── README.md ├── advanced-patterns/ │ └── advanced-patterns.pq ├── api-connections/ │ └── api-connection-templates.pq ├── data-cleaning/ │ └── data-cleaning-templates.pq ├── data-transformation/ │ └── transformation-templates.pq ├── date-tables/ │ └── date-table-templates.pq ├── file-consolidation/ │ └── file-consolidation-templates.pq ├── free-sample.zip ├── fuzzy-matching/ │ └── fuzzy-matching-templates.pq ├── guide/ │ ├── common-errors.md │ ├── m-function-reference.md │ ├── power-query-patterns-guide.md │ └── quickstart.md ├── guides/ │ ├── common-errors.md │ ├── m-function-reference.md │ ├── power-query-patterns-guide.md │ └── quickstart.md ├── index.html ├── interactive.html ├── pivot-unpivot/ │ └── pivot-unpivot-templates.pq └── reporting-workflows/ └── reporting-workflow-templates.pq

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.