PowerBI-Dataflow-Gen1-Skill

Claude Code skills for reviewing and improving Power Query M in legacy Power BI Dataflows Gen1.

Why this exists

Some organizations cannot enable Dataflow Gen2 because of governance, licensing, platform, or internal policy constraints. This project is for those teams. It helps an analyst or data engineer work from the artifacts they can safely share: M queries, custom functions, parameters, dataflow definition JSON, refresh errors, and transformation requirements.

The workflow is privacy-conscious and code-first. It does not require a Power BI tenant, workspace, semantic model, gateway, credentials, customer data, or live connector. It is designed for situations where sharing BI access or production data would be inappropriate under company policy or privacy obligations such as LGPD.

What it does

• Reviews and rewrites Power Query M while preserving intended semantics.
• Identifies likely improvements in query folding, step order, typing, joins, expansions, buffering, and refresh cost.
• Designs and reviews reusable M functions and parameters.
• Diagnoses Dataflow Gen1 refresh and dependency patterns from supplied text or exported definitions.
• Recommends bounded Gen1 architectures instead of blindly applying a bronze/silver/gold pattern.
• Returns code, assumptions, risks, and validation steps in a repeatable format.

What it does not do

• It does not execute M, inspect a live model, verify query folding, run a refresh, or test a gateway.
• It does not contain DAX, semantic-model, PBIP/TMDL, report, dashboard, storytelling, or general UI guidance.
• It does not replace runtime validation in Power BI Service or at the source system.

Skills

Skill	Use it for
dataflow-gen1-m-review	Route a Gen1/M request and return a code-first review or rewrite
power-query-m-optimization	Optimize M correctness, folding opportunities, and refresh cost
dataflow-gen1-functions	Design reusable M functions, parameters, and error handling
dataflow-gen1-refresh	Review Gen1 refresh, linked/computed entities, incremental refresh, and API-shaped maintenance
dataflow-gen1-architecture	Choose a practical number of Gen1 layers and dependencies

Install in Claude Code

Skills CLI

npx skills add Caio-Analytics/PowerBI-Dataflow-Gen1-Skill

Manual installation

git clone https://github.com/Caio-Analytics/PowerBI-Dataflow-Gen1-Skill.git
mkdir -p ~/.claude/skills
cp -R PowerBI-Dataflow-Gen1-Skill/skills/* ~/.claude/skills/

Restart Claude Code, or start a new session, after installation. To install only one skill, copy only its directory from skills/ into ~/.claude/skills/.

Example prompts

The examples below use synthetic values only. In practice, replace the sample artifact with your own sanitized M code or inventory.

Review this Dataflow Gen1 M query. Preserve the result, identify likely folding breaks,
and return the complete optimized query with a concise change log:

let
    Source = Table.FromRecords({
        [OrderId = 1001, Status = "Active", UpdatedAt = #datetime(2026, 1, 5, 9, 30, 0)],
        [OrderId = 1002, Status = "Inactive", UpdatedAt = #datetime(2026, 1, 6, 10, 0, 0)]
    }),
    Expanded = Table.ExpandRecordColumn(
        Table.AddColumn(Source, "Audit", each [UpdatedAt = [UpdatedAt]]),
        "Audit",
        {"UpdatedAt"},
        {"AuditUpdatedAt"}
    ),
    Filtered = Table.SelectRows(Expanded, each [Status] = "Active"),
    Typed = Table.TransformColumnTypes(Filtered, {
        {"OrderId", Int64.Type},
        {"Status", type text},
        {"UpdatedAt", type datetime},
        {"AuditUpdatedAt", type datetime}
    })
in
    Typed

Turn this repeated transformation into a reusable M function for Dataflow Gen1.
State the input/output types, null behavior, error behavior, and an invocation example:

let
    Source = Table.FromRecords({
        [CustomerId = 1, RawStatus = " active "],
        [CustomerId = 2, RawStatus = null],
        [CustomerId = 3, RawStatus = "blocked"]
    }),
    AddNormalizedStatus = Table.AddColumn(
        Source,
        "NormalizedStatus",
        each if [RawStatus] = null then null else Text.Upper(Text.Trim([RawStatus])),
        type nullable text
    )
in
    AddNormalizedStatus

I have one source dataflow and three downstream dataflows. Gen2 is unavailable.
Based on these refresh cadences and entity counts, recommend the smallest practical
Gen1 architecture and explain whether another layer is justified:

source_dataflow:
  name: SalesSource
  entities: 12
  refresh: "daily 05:00"
downstream_dataflows:
  - name: SalesCurated
    entities: 8
    refresh: "after source"
  - name: FinanceSummary
    entities: 4
    refresh: "daily 07:00"
  - name: OperationsExtract
    entities: 3
    refresh: "daily 08:00"
gen2_available: false
known_issue: "linked navigation query reports a write-back error when transformed directly"

Response contract

For code reviews, the skills aim to return:

1. Assessment
2. Final M code, when code was supplied
3. Changes made
4. Semantic and Gen1 compatibility risks
5. Validation steps the user can run

If source schema, privacy level, connector behavior, or runtime evidence is missing, the response must label the assumption instead of inventing certainty.

Gen1 architecture principle

Microsoft documents useful staging, linked, and computed-dataflow patterns, but also documents refresh-chain, linked-entity, script-size, table-count, and evaluation-resource constraints. This project therefore treats a full medallion architecture as an option to evaluate. A single dataflow or a bounded staging-plus-curated design is considered first; each additional layer must justify its dependency and refresh cost.

When one Gen1 dataflow consumes another, preserve the connector-generated source/navigation query as a read-only staging query and reference it from the downstream transformation query. When applicable, keep load disabled on the navigation/staging query and materialize only the resulting table. This avoids treating an upstream linked/read-only table as if it accepted local changes; transformations belong in the downstream computed/reference table. Confirm the exact behavior for the workspace/storage configuration and preserve the navigation steps generated by the connector.

Sources

The detailed source index is in SOURCES.md. The skills distill official Microsoft documentation and link back to it; they do not copy entire manuals.

License

MIT. See LICENSE.