Skip to main content
skill CX Codex

power-query-m — Claude Code skill: what it does, how to install

This skill helps write Power Query M, diagnose query folding, and build ETL transformations. Use it for parameters, dynamic sources, and connector work.

MIT 114 installs ★ 66 updated 2026-06-18

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a claude-code
Open on GitHub

What power-query-m does

The skill covers Power Query M query structure and common transformations, including filtering, joins, appends, pivots, unpivots, type changes, and column operations. It also provides patterns for List.Generate, Record.Field, parameterized connections, dynamic sources, and error handling with try/otherwise.

It explains how to inspect Native Query output and use Query Diagnostics to find where folding stops. The material also covers reducing source data, partition strategy, and custom connector work with the Power Query SDK. Once Table.Buffer materializes a table, later steps cannot fold back to the source.

The skill can be installed for Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, and OpenCode. For Claude Code, run the install command with npx; the installed skill file is ~/.claude/skills/power-query-m/SKILL.md.

What it helps with

  • write a Power Query M ETL query
  • find the step that breaks query folding
  • merge, append, pivot, or unpivot tables
  • build a parameterized dynamic source
  • handle errors in an M query

Example prompts for the agent

  • Write Power Query M that filters active rows and converts Amount to a number.
  • Inspect query folding for this query and identify the step where folding stops.
  • Create a paginated fetch with List.Generate and combine the results into a table.

How to install power-query-m

1Skills CLI (Vercel)

npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a claude-code

2Manually: put the files in place

~/.claude/skills/power-query-m/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/power-query-m/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about power-query-m

How do I install power-query-m in Claude Code?

Run npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a claude-code, then check ~/.claude/skills/power-query-m/SKILL.md.

What does power-query-m do?

It assists with M code for ETL and table transformations, query-folding diagnosis, dynamic source patterns, and error handling. It also covers custom connector development.

Which platforms support power-query-m?

The skill is available for Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, and OpenCode.

Source text

SKILL.md · MIT
---
name: power-query-m
description: |
  Power Query M language for ETL, transformations, and connector authoring.
  PROACTIVELY activate for: (1) writing Power Query M code, (2) query folding and Native Query verification, (3) data transformations (merge, append, pivot, unpivot, split column), (4) custom connector development, (5) Power Query parameters and dynamic source patterns, (6) performance optimization (folding, query reduction, partition strategy), (7) Table.TransformColumns, List.Generate, Record.Field functions, (8) error handling in M (try/otherwise, error records), (9) mashup engine internals, (10) ETL pipelines from folder/SharePoint/database sources.
  Provides: M language reference, query-folding diagnosis, transformation recipes, dynamic source patterns, error-handling templates, and performance tuning techniques.
---

# Power Query (M Language)

## Overview

Power Query is the data transformation engine in Power BI, using the M functional language. It handles ETL (Extract, Transform, Load) from sources to the data model. Understanding query folding, step optimization, and M syntax is critical for performant data refresh.

## Query Folding

Query folding translates M steps into native source queries (SQL, OData, etc.), pushing computation to the source instead of the mashup engine.

**How to check folding:**
1. Right-click a step in Applied Steps > "View Native Query" -- if grayed out, folding broke
2. Use Query Diagnostics (Tools > Start Diagnostics) to see what queries are sent

**Steps that fold (common):**
| Operation | SQL Translation |
|-----------|----------------|
| Remove columns | SELECT (column list) |
| Filter rows | WHERE clause |
| Sort rows | ORDER BY |
| Group by | GROUP BY |
| Rename columns | Column aliases |
| Change type (basic) | CAST |
| Merge queries (database) | JOIN |
| Top N rows | TOP / LIMIT |
| Remove duplicates | DISTINCT |

**Steps that break folding:**
| Operation | Why |
|-----------|-----|
| Add custom column (complex) | M expression cannot translate to SQL |
| Pivot/Unpivot (sometimes) | Depends on source capability |
| Merge with non-foldable source | Cannot push cross-source joins |
| Table.Buffer | Explicitly materializes in memory |
| Reorder after custom step | Once broken, subsequent steps cannot fold |
| Date/time transforms (some) | Source-specific function differences |

**Golden rule:** Put foldable steps BEFORE non-foldable steps. Once folding breaks, all subsequent steps run in the mashup engine.

## M Language Essentials

### Let Expression (Query Structure)

Every Power Query query is a `let...in` expression:

```m
let
    Source = Sql.Database("server", "database"),
    Filtered = Table.SelectRows(Source, each [Status] = "Active"),
    Renamed = Table.RenameColumns(Filtered, {{"OldName", "NewName"}}),
    Typed = Table.TransformColumnTypes(Renamed, {{"Amount", type number}})
in
    Typed
```

### Data Types

| M Type | Description |
|--------|-------------|
| `type text` | String/text |
| `type number` | Decimal number |
| `Int64.Type` | Whole number (64-bit integer) |
| `type date` | Date only |
| `type datetime` | Date and time |
| `type datetimezone` | Date, time, and timezone |
| `type duration` | Time duration |
| `type logical` | Boolean (true/false) |
| `type binary` | Binary data |
| `type null` | Null value |
| `Currency.Type` | Fixed decimal (4 places) |
| `Percentage.Type` | Percentage |

### Common Table Functions

```m
// Filter rows
Table.SelectRows(table, each [Column] > 100)

// Add column
Table.AddColumn(table, "NewCol", each [Col1] * [Col2], type number)

// Remove columns
Table.RemoveColumns(table, {"Col1", "Col2"})

// Select columns (keep only these)
Table.SelectColumns(table, {"Col1", "Col2", "Col3"})

// Rename columns
Table.RenameColumns(table, {{"Old1", "New1"}, {"Old2", "New2"}})

// Change types
Table.TransformColumnTypes(table, {{"Col1", type number}, {"Col2", type text}})

// Replace values
Table.ReplaceValue(table, "old", "new", Replacer.ReplaceText, {"Column"})

// Group by
Table.Group(table, {"GroupCol"}, {
    {"Sum", each List.Sum([Amount]), type number},
    {"Count", each Table.RowCount(_), Int64.Type}
})

// Merge (JOIN)
Table.NestedJoin(left, {"KeyCol"}, right, {"KeyCol"}, "Merged", JoinKind.LeftOuter)

// Expand merged columns
Table.ExpandTableColumn(merged, "Merged", {"Col1", "Col2"})

// Pivot
Table.Pivot(table, List.Distinct(table[PivotCol]), "PivotCol", "ValueCol")

// Unpivot
Table.UnpivotOtherColumns(table, {"KeepCol1", "KeepCol2"}, "Attribute", "Value")

// Sort
Table.Sort(table, {{"Col1", Order.Ascending}, {"Col2", Order.Descending}})

// Remove duplicates
Table.Distinct(table, {"KeyCol1", "KeyCol2"})

// Combine/Append tables
Table.Combine({table1, table2, table3})

// Buffer (force materialization)
Table.Buffer(table)
```

### List Functions

```m
// Generate a sequence
{1..100}
List.Numbers(1, 100)
List.Dates(#date(2024,1,1), 365, #duration(1,0,0,0))

// Transform
List.Transform({1,2,3}, each _ * 2)

// Filter
List.Select({1,2,3,4,5}, each _ > 3)

// Aggregate
List.Sum(list), List.Average(list), List.Min(list), List.Max(list)

// Generate with custom logic (pagination pattern)
List.Generate(
    () => [Page = 0, Data = GetPage(0)],
    each [Data] <> null,
    each [Page = [Page] + 1, Data = GetPage([Page] + 1)],
    each [Data]
)
```

## Parameters and Dynamic Sources

Create parameters for environment-specific connections:

```m
// Define parameter in Power Query UI or M:
// Name: ServerName, Type: Text, Current Value: "prod-server.database.windows.net"

// Use in query:
let
    Source = Sql.Database(ServerName, DatabaseName),
    ...
```

**Dynamic source pattern:**
```m
let
    BaseUrl = "https://api.example.com/v2/",
    Endpoint = BaseUrl & "data?page=",
    GetPage = (pageNum as number) =>
        let
            url = Endpoint & Number.ToText(pageNum),
            response = Json.Document(Web.Contents(url))
        in
            response[results],
    AllPages = List.Generate(
        () => [i = 1, res = GetPage(1)],
        each List.Count([res]) > 0,
        each [i = [i] + 1, res = GetPage([i] + 1)],
        each [res]
    ),
    Combined = List.Combine(AllPages),
    AsTable = Table.FromList(Combined, Record.FieldValues,
        type table [id = Int64.Type, name = text, value = number])
in
    AsTable
```

## Error Handling

```m
// Try/otherwise pattern
let
    result = try SomeRiskyOperation() otherwise "default"
in
    result

// Try with error record inspection
let
    attempt = try Number.FromText("abc"),
    output = if attempt[HasError]
        then "Error: " & attempt[Error][Message]
        else attempt[Value]
in
    output

// Replace errors in a column
Table.ReplaceErrorValues(table, {{"Column1", null}, {"Column2", 0}})

// Remove error rows
Table.RemoveRowsWithErrors(table, {"Column1", "Column2"})
```

## Custom Connectors

Build custom Power Query connectors using the Power Query SDK:

1. Install Power Query SDK (VS Code extension)
2. Create a `.mproj` project with `DataConnector.pq` file
3. Implement the data source function with authentication
4. Package as `.mez` file
5. Deploy to `Documents\Power BI Desktop\Custom Connectors` or gateway

**Basic connector structure:**
```m
section MyConnector;

[DataSource.Kind="MyConnector", Publish="MyConnector.Publish"]
shared MyConnector.Contents = (url as text) =>
    let
        source = Web.Contents(url),
        json = Json.Document(source)
    in
        json;

MyConnector = [
    Authentication = [
        Key = [],
        OAuth = [...]
    ],
    Label = "My Custom Connector"
];

MyConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = {"My Connector", "Connect to My Service"}
];
```

## Performance Optimization

| Technique | Impact |
|-----------|--------|
| Put foldable steps first | High -- pushes work to source |
| Remove unused columns early | High -- reduces data volume |
| Filter early, before joins | High -- reduces row count |
| Avoid Table.Buffer unless needed | Medium -- prevents unnecessary materialization |
| Use native queries when folding fails | High -- bypass mashup engine |
| Disable "Include in report refresh" for staging queries | Medium -- skips unnecessary refresh |
| Use Table.Partition for parallel loading | Medium -- parallelizes large tables |
| Set Privacy Levels correctly | Medium -- incorrect levels block folding |

## Additional Resources

### Reference Files
- **`references/m-patterns-cookbook.md`** -- Common M patterns: web API pagination, incremental load, JSON flattening, CSV handling, SharePoint folder combine

© JosiahSiegel · MIT · JosiahSiegel/claude-plugin-marketplace

Similar skills

All skills for Data and databases →

From the same set96

The whole set →
skill-development skill-development — a guide to authoring SKILL.md files for Claude plugin skills: frontmatter rules, folder layout, and fixes for skills that never fire. CX Codex +1 116installs agent-development agent-development is a Claude Code skill for configuring agent frontmatter and system prompts. It supports agent creation and diagnosing why an agent fails to trigger. CX Codex +1 116installs ffmpeg-stabilization-360 The ffmpeg-stabilization-360 Claude Code skill handles shaky footage and 360/VR projection conversion. It activates for stabilization, lens correction, perspective fixes, and zoom-pan effects. CX Codex +1 115installs viral-video-long-form The viral-video-long-form Claude Code skill supports planning long YouTube videos and improving their SEO, audience retention, and thumbnails. It activates for video strategy, content planning, and analytics interpretation. CX Codex +1 114installs adf-ml-analytics adf-ml-analytics — a skill that maps Azure Data Factory onto Azure ML, Databricks and AI Services: batch scoring, notebook runs, SQL archival. Useful when a pipeline still sits on SDK v1. CX Codex +1 113installs ffmpeg-filter-complex-patterns The ffmpeg-filter-complex-patterns Claude Code skill explains FFmpeg’s complex filter syntax. It activates for multi-source video layouts, transitions, audio mixing, and stream routing questions. CX Codex +1 112installs hook-development hook-development — a practical guide to building Claude Code hooks, from checks before a tool runs to tests after file changes. Use it to configure hook behavior or investigate why one does not fire. CX Codex +1 112installs ffmpeg-video-analysis The ffmpeg-video-analysis Claude Code skill uses FFmpeg filters to inspect video and measure quality. It activates for checks such as black or frozen frames, blur, scene changes, and crop detection. CX Codex +1 111installs

More in the catalog

Project sponsors

Companies that keep this analytics open