← 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.

📁 22 files
MarkdownExcelPower 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 22 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 ├── 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.pages.dev/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.

Buy Now — $29 Back to Products