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
npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a claude-codeWhat 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
Amountto a number. - Inspect query folding for this query and identify the step where folding stops.
- Create a paginated fetch with
List.Generateand 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-code2Manually: put the files in place
~/.claude/skills/power-query-m/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/power-query-m/
1Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a codex2Manually: put the files in place
.agents/skills/power-query-m/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a cursor2Manually: put the files in place
.cursor/skills/power-query-m/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install JosiahSiegel/claude-plugin-marketplace plugins/powerbi-master/skills/power-query-m2Manually: put the files in place
.github/skills/power-query-m/SKILL.md1Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a gemini-cli2Platform CLI
gemini skills install https://github.com/JosiahSiegel/claude-plugin-marketplace --path plugins/powerbi-master/skills/power-query-mManually: put the files in place
.devin/skills/power-query-m/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill power-query-m -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
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