powerbi-power-query
Skill3 starsChanged 7 months agoMITView on GitHub
Author, validate, and test Power Query M expressions in semantic model partitions. Automatically invoke when the user mentions "Power Query", "M code", "M expression", "partition expression", "query folding", or asks to "write Power Query", "fix Power Query", "test a partition", "preview partition data", "debug Power Query step", "optimize Power Query".
1 other project uses a version of this.
Adopted by
Most recently taken up by
- mranceXXIII/PBIdevtheir own version, first seen Nov 15, 2026
Other versions1 project uses a version of this.
Word for word · 0
None found.
Edited · 0
None found.
Wrote their own version · 1
- mranceXXIII/PBIdev★ 0same name
How matches are found. Word for word: the exact same text. Edited: shares passages with this file (“99% same” means 99% of its five-word phrases also appear here; 10% or more counts). Their own version: similar meaning, or, weakest, the same name. We can’t tell which project had it first. Projects are counted among the 200 most-starred candidates, and mentions are links to the repository we found, up to 50 per site, so both counts are a floor.
In this repository58 other files it gives its agents.
- powerbi-audit-tenant-settingsSkill
- powerbi-bpa-rulesSkill
- powerbi-connect-pbidSkill
- powerbi-connectSkill
- powerbi-create-pbi-reportSkill
- powerbi-c-sharp-scriptingSkill
- powerbi-custom-visualsSkill
- powerbi-cyber-securitySkill
- powerbi-data-catalog-lineageSkill
- powerbi-data-governance-traceabilitySkill
- powerbi-data-transformationSkill
- powerbi-dax-masterySkill
- powerbi-dax-performanceSkill
- powerbi-daxSkill
- powerbi-deneb-visualsSkill
- powerbi-doctorSkill
- powerbi-duckdb-lakehouseSkill
- powerbi-fabric-appsSkill
- powerbi-fabric-app-templatesSkill
- powerbi-fabric-capacitySkill
- powerbi-fabric-cliSkill
- powerbi-fabric-pipelinesSkill
- powerbi-fabricSkill
- powerbi-lineage-analysisSkill
- powerbi-measure-glossarySkill
- powerbi-medallion-architectureSkill
- powerbi-modelSkill
- powerbi-modifying-theme-jsonSkill
- powerbi-paginated-reportsSkill
- powerbi-pbi-cliSkill
- powerbi-pbip-formatSkill
- powerbi-pbir-cliSkill
- powerbi-pbir-format-enhancedSkill
- powerbi-performance-scaleSkill
- powerbi-project-managementSkill
- powerbi-python-visualsSkill
- powerbi-refresh-semantic-modelSkill
- powerbi-report-authoringSkill
- powerbi-report-conversionSkill
- powerbi-report-designSkill
- powerbi-reportSkill
- powerbi-report-structureSkill
- powerbi-report-themingSkill
- powerbi-review-reportSkill
- powerbi-review-semantic-modelSkill
- powerbi-r-visualsSkill
- powerbi-security-rlsSkill
- powerbi-source-integrationSkill
- powerbi-spark-livySkill
- powerbi-standardize-naming-conventionsSkill
- powerbi-star-schema-modelingSkill
- powerbi-svg-visualsSkill
- powerbi-te2-cliSkill
- powerbi-te-cliSkill
- powerbi-te-docsSkill
- powerbi-testing-validationSkill
- powerbi-time-series-dataSkill
- powerbi-tmdlSkill
The file
We split this skill into the rules it gives an agent
10 rules. About 0 are common, 5 are distinctive, and 5 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈5
Distinctive · estimate
This author's own choices. Worth a look.
5
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 74
Remove unused columns and filter rows as early as possible:
- Very specific: line 92
Use explicit M types: Int64.Type, type text, type date, Currency.Type, type logical
- Distinctive: line 93
Avoid implicit type inference on large datasets
- Very specific: line 98
Avoid generic names like #"Custom1" or #"Step1"
- Very specific: line 99
Use quoted identifiers #"Name" for steps with spaces (Power Query convention)
- Distinctive: line 107
Test the expression by running it against real data.
- Distinctive: line 111
Create or reuse a runner dataflow in the workspace
- Distinctive: line 135
Write the expression back to the model.
- Very specific: line 174
Add Table.FirstN(stepName, 100) before the in to limit rows for large tables.
- Very specific: line 192
When testing, inline concrete date values for RangeStart and RangeEnd.
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Power Query for Semantic Models
Author, validate, and test Power Query M expressions in semantic model import partitions. Covers writing correct M code, preserving query folding, validating expressions, and testing them by executing against real data sources.
Partition Expressions
Each import table in a semantic model has a partition with an M expression defining what data gets loaded during refresh. The expression typically connects to a data source, navigates to a table/view, and applies transformations.
Structure of a Partition Expression
let
Source = Sql.Database(#"SqlEndpoint", #"Database"),
Data = Source{[Schema="dbo", Item="Orders"]}[Data],
#"Removed Columns" = Table.RemoveColumns(Data, {"InternalId"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns", {{"Amount", Currency.Type}})
in
#"Changed Type"Key elements:
- Parameters:
#"SqlEndpoint",#"Database"are shared M parameters defined at the model level - Navigation:
Source{[Schema="dbo", Item="Orders"]}[Data]navigates to a specific table - Steps: Each step is a named variable in the
let...inchain - Quoted identifiers: Step names with spaces use
#"Step Name"syntax
Extracting Expressions
# Get partition expression from TMDL via fab
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
-q "definition.parts[?path=='definition/tables/<Table>.tmdl'].payload"
# Get shared M parameters
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
-q "definition.parts[?path=='definition/expressions.tmdl'].payload"Writing M Expressions
Query Folding
Query folding is the most important performance concept. The M engine translates compatible steps into native data source queries (e.g., SQL). When folding breaks, subsequent steps run in the mashup engine, pulling all data into memory first.
Steps that typically fold (for SQL sources):
Table.SelectColumns/Table.RemoveColumns->SELECTTable.SelectRows->WHERETable.Sort->ORDER BYTable.FirstN->TOPTable.Group->GROUP BYTable.RenameColumns->ASaliases
Steps that may or may not fold (source-dependent):
Table.TransformColumnTypes-- frequently breaks folding for text-to-numeric/date conversions on SQL Server sources. UseTable.TransformColumnswith explicit conversion functions (e.g.,Number.From) as a more reliable foldable alternative.
Steps that break folding:
Table.AddColumnwith custom M functions (not translatable to SQL)Table.Buffer(forces materialization; preferTable.StopFoldingto stop folding without the memory overhead)Table.LastN(no SQL equivalent without subquery)Table.Combineacross different data sources (cross-database folding within the same SQL Server is possible viaEnableCrossDatabaseFolding)- Complex
eachexpressions with M-specific logic - Any step after a fold-breaking step
Best practice: Apply folding-compatible steps (filter, select, type) early; add custom columns and M-only transforms after all foldable work is done.
Column Pruning and Row Filtering
Remove unused columns and filter rows as early as possible:
let
Source = Sql.Database(SqlEndpoint, Database),
Data = Source{[Schema="dbo", Item="Orders"]}[Data],
// Filter and select BEFORE any custom transforms
#"Filtered" = Table.SelectRows(Data, each [Status] <> "Cancelled"),
#"Selected" = Table.SelectColumns(#"Filtered", {"OrderId", "Date", "Amount", "CustomerId"})
in
#"Selected"These steps fold to SQL: SELECT OrderId, Date, Amount, CustomerId FROM dbo.Orders WHERE Status <> 'Cancelled'
Type Handling
- Apply
Table.TransformColumnTypesearly (folds toCASTin SQL) - Use explicit M types:
Int64.Type,type text,type date,Currency.Type,type logical - Avoid implicit type inference on large datasets
Naming Conventions
- Step names should describe the transformation:
#"Removed Duplicates",#"Filtered Active" - Avoid generic names like
#"Custom1"or#"Step1" - Use quoted identifiers
#"Name"for steps with spaces (Power Query convention)
Validating M Expressions
Two approaches to validate that an M expression is syntactically correct and produces expected results:
1. Execute via the Power Query API (Recommended)
Test the expression by running it against real data. This validates syntax, data source connectivity, and transformation correctness in one step.
The executing-power-query skill in the etl plugin provides the full workflow. In summary:
- Create or reuse a runner dataflow in the workspace
- Bind the data source connection to the runner
- Wrap the expression in a section document, inline parameters
- Execute via
POST /v1/workspaces/{wsId}/dataflows/{dfId}/executeQuery - Parse the Arrow response to verify data
MASHUP='section Section1;
shared SqlEndpoint = "myserver.database.windows.net";
shared Database = "MyDB";
shared Result = let
Source = Sql.Database(SqlEndpoint, Database),
Data = Table.FirstN(Source{[Schema="dbo",Item="Orders"]}[Data], 10)
in Data;'
curl -s -o result.bin -X POST ".../executeQuery" \
-H "Authorization: Bearer ${TOKEN}" -H "Content-Type: application/json" \
-d "$(jq -n --arg m "$MASHUP" '{queryName:"Result",customMashupDocument:$m}')"See references/validation.md for step-by-step instructions and error handling.
2. Save the Partition via XMLA / TOM
Write the expression back to the model; Analysis Services validates the M syntax on save. This doesn't execute the query but catches structural errors:
- Missing or mismatched
let/in - Undefined step references
- Invalid function calls
- Type mismatches in
TransformColumnTypes
# Edit the TMDL partition source directly and deploy via fab import,
# or use the XMLA endpoint with Tabular Editor or SSMS to modify
# the partition expression on the deployed model.AS returns an error if the expression is malformed. This is faster than a full execute but doesn't catch runtime errors (wrong column names, data source issues).
Choosing a Validation Approach
| Need | Use |
| Full data validation (correct columns, types, values) | Execute via API |
| Quick syntax check | Save to model via XMLA/TOM |
| Step-by-step debugging | Execute with truncated in clause |
| Performance testing (check folding) | Execute with full data, observe timing |
Previewing Partition Steps
See the data at any point in the transformation chain by truncating the let...in:
-- See raw source data (all columns)
in Data;
-- See after column removal
in #"Removed Columns";
-- See final result
in #"Changed Type";Add Table.FirstN(stepName, 100) before the in to limit rows for large tables. See references/validation.md for the complete procedure.
Common Patterns
Incremental Refresh Partitions
Incremental refresh partitions use RangeStart and RangeEnd parameters:
let
Source = Sql.Database(#"SqlEndpoint", #"Database"),
Data = Source{[Schema="dbo", Item="Orders"]}[Data],
#"Filtered" = Table.SelectRows(Data, each
[OrderDate] >= #"RangeStart" and [OrderDate] < #"RangeEnd")
in
#"Filtered"When testing, inline concrete date values for RangeStart and RangeEnd.
Lakehouse Sources
let
Source = Lakehouse.Contents(null),
Data = Source{[Id="lakehouse-guid"]}[Data],
Table = Data{[Id="table-name", ItemKind="Table"]}[Data]
in
TableSQL with Native Query
For complex SQL that can't be expressed in M:
let
Source = Sql.Database("server", "db"),
Data = Value.NativeQuery(Source, "SELECT * FROM dbo.MyView WHERE Year = 2024", null, [EnableFolding=true])
in
DataValue.NativeQuery with EnableFolding=true allows subsequent M steps to fold on top of the native query.
References
references/validation.md-- Detailed validation workflow with executeQuery API, step preview, error handlingreferences/best-practices.md-- Query folding guidance, fold-breaker list, anti-patterns, performance tipsexamples/execute_m.py-- Python script to execute M expressions via the Fabric API (CLI tool)examples/preview_partition.py-- Python script to preview partition data at any step (usesfab get+execute_m.py)- Power Query M Reference
- Query Folding Guidance
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

