malloy-powerbi-review

Files (16)

• NOTICE
• SKILL.md
• _concepts.md · reference/
• cookbook-filter-context.md · reference/
• cookbook-structure.md · reference/
• cookbook-time.md · reference/
• corpus.md · reference/
• discover.md · reference/
• document.md · reference/
• limitations.md · reference/
• propose-fields.md · reference/
• review-coverage.md · reference/
• rls-roles.md · reference/
• translate-measures.md · reference/
• classify_measures.py · scripts/
• classify_measures_test.py · scripts/

SKILL.md (first lines)

<!--
Copyright (c) Credible Data Inc.
SPDX-License-Identifier: MIT
-->

# Power BI to Malloy

> **Purpose:** Move a working Power BI semantic model onto Malloy, fast, and prove the numbers match. The user already has a model that their business agreed on; the job is to carry it across without silently changing what it means.

> **Tool names** are written bare here - `get_context`, `execute_query`, `search_malloy_docs`. The exact prefixed name depends on the host surface; match each against the tools you actually have.

> **This is NOT a blind conversion.** DAX and Malloy disagree about what a filter means. A measure that translates cleanly on sight can still return a different number, with no error raised. Knowing which measures change meaning on the way across is the work - and then saying what the port costs, because "untranslatable" has done nothing for the customer.

## The migration, end to end

1. **Get the model in text.** Ask for TMDL or PBIP before accepting a `.pbix` (see below). `reference/discover.md` inventories it. *(Workflow Step 1, DISCOVER.)*
2. **Carry across what the business already agreed on** - tables, relationships, names, descriptions, visibility. `reference/propose-fields.md`. *(Step 4, DEFINE.)*
3. **Route every measure, then transpile it from a recipe.** `reference/translate-measures.md` decides where each one goes; the three `cookbook-*.md` files have the worked Malloy. A routing table is not a migration - the recipe is the deliverable. *(Step 5, BUILD.)*
4. **Prove the numbers.** Parity at more than one filter context, below. This is what makes the switch safe to recommend rather than merely done. *(Steps 5 and 7.)*
5. **Say what changed.** Every divergent measure, every stopgap and its cost, every RLS role that came across weaker. `reference/review-coverage.md` checks nothing was dropped; `rls-roles.md` and `document.md` finish the job. *(Steps 7 to 9.)*

`Step N` here and in the reference files is the numbering of the shared modeling workflow (`malloy-modeling`); the five items above are where this migration's stages land in it.

The user's question is "can I move off Power BI and still trust my numbers?" Items 3 and 4 are the answer; the rest is bookkeeping.

## When to Use

- **Auto-detected:** The agent finds a `.pbix`, a `.pbip` project, or a `definition/` folder of `.tmdl` files and the user confirms they want it carried across.
- **Explicitly requested:** The user says "migrate off Power BI", "switch from Power BI", "model from Power BI", "convert this PBIX", or provides a path to Power BI artifacts.
- **One measure at a time:** The user pastes a DAX expression and asks what it becomes in Malloy. Go straight to the recipe in `reference/cookbook-*.md`; the routing procedure is for a whole model.

## Two Modes

| Mode | When | Behavior |
|------|------|----------|
| **Power BI + live data** | A connection is configured against the same warehouse the model reads | Power BI provides prior art; the data validates it. Full data-driven proposals. |
| **Power BI only** | No connection, or the model is import-mode with no warehouse behind it | The model file provides all context. Proposals flagged as **unvalidated**. |

In Power BI-only mode, warn the user: "No database connection found. I'll use the Power BI model as the sole source of context, but proposals cannot be validated against live data."

## Input Shapes: Prefer Text Over Binary

Three shapes arrive, and they are not equally trustworthy. Establish which one you have before anything else.

| Shape | What it is | How to read it |
|-------|-----------|----------------|
| **TMDL folder** (`definition/*.tmdl`) | The model in text form | Read directly. No extraction step, nothing to go wrong. **Preferred.** |
| **PBIP project** (`*.SemanticModel/definition/`) | A `.pbip` save format that contains the TMDL folder above | Read the TMDL directly, same as above. |
| **`.pbix`** | A zip whose model is a compressed binary part | Requires third-party extraction. **See the parity section below before you trust a number out of it.** |

**Always ask for TMDL or PBIP before accepting a `.pbix`.** It removes the entire extraction risk class from the migration. Power BI Desktop saves to PBIP directly, but it is still a **preview** feature that has to be enabled under `Options > Preview features` first, and it is unavailable in Desktop for Report Server, so ask with those instructions rather than just naming the format (`reference/discover.md` has the wording). A `.pbix` is what you fall back to when the user cannot re-save.

The `.pbix` also carries the **data**, where TMDL carries only the **model**. If the goal is a working model over a warehouse the user still has, TMDL is sufficient. If the goal includes lifting the imported data out, see `reference/discover.md`.

## Numeric Parity Validation (do this before you trust anything)

Two independent mechanisms can hand back a wrong number with no error. Neither announces itself.

**1. Extraction can be silently wrong.** Reading a `.pbix` means a reverse-engineered reader, not a Microsoft tool. The readers available have open issues in which a column decodes to *plausible but wrong values* rather than failing. A wrong number that looks like a number survives every check except comparison against the source. This risk does not exist on the TMDL path, which is one more reason to ask for it.

**2. DAX and Malloy disagree about filters.** This one applies on every path, including TMDL, and it is the one that costs a migration its credibility. A DAX Boolean filter argument **overwrites** the existing filter on that column; Malloy's `where:` **intersects**. The same measure, faithfully transcribed, answers a different question. `reference/translate-measures.md` is entirely about telling these apart.

**So validate numerically, not visually:**

1. Pick the measures the business actually looks at, not the easy ones. Ask the user which numbers are on the dashboard people open every morning.
2. Get the current value from Power BI itself, at a stated filter context (a specific year, region, whatever the report shows). A screenshot of the report is fine and is often faster than arranging API access.
3. Run the Malloy equivalent at the same filter context with `execute_query` and compare.
4. Compare at more than one filter context. The overwrite-versus-intersect divergence is **invisible at the grand total** and only appears once a filter on the same column is active. A measure that matches unfiltered and diverges when sliced is the signature of this bug, not a coincidence.
5. For a lifted `.pbix`, also compare row counts and one column sum per table before modeling anything on top of it.

**Report parity as a table of measure, context, Power BI value, Malloy value, and match.** "It looks right" is not a result. A migration is trusted or abandoned on these numbers.

## Reference Files

Each reference file is loaded by the workflow phase that needs it. You do not need to read them all at once.

| Reference File | Phase | What It Does |
|------------|-------|-------------|
| `reference/discover.md` | Step 1 (DISCOVER) | Inventory the model, classify storage mode, extract source candidates, capture prior-art notes |
| `reference/propose-fields.md` | Step 4 (DEFINE) | Extract field proposals from tables, columns, and relationships |
| `reference/translate-measures.md` | Step 5 (BUILD) | Route every DAX measure to a recipe, and say what the routing does not prove |
| `reference/cookbook-filter-context.md` | Step 5 (BUILD) | Worked transpiles: `CALCULATE`, `ALL` / `ALLSELECTED` / `ALLEXCEPT`, ranking, top-N |
| `reference/cookbook-time.md` | Step 5 (BUILD) | Worked transpiles: YTD, prior period, growth, date spines, semi-additive |
| `reference/cookbook-structure.md` | Step 5 (BUILD) | Worked transpiles: role-playing dimensions, M:M, bidirectional, what-if parameters, hierarchies |
| `reference/rls-roles.md` | Step 8 (CURATE) | Map RLS roles and hidden objects to access modifiers and gate annotations |
| `reference/review-coverage.md` | Step 7 (REVIEW) | Compare the Malloy model against the Power BI model: table, measure, and relationship coverage |
| `reference/document.md` | Step 9 (DOCUMENT) | Extract TMDL descriptions as `#(doc)` tag seeds |
| `reference/limitations.md` | any | What the script reads and what it does not, and the calls it cannot make from the model file |

`reference/corpus.md` is not part of the workflow: it is the provenance of the public
models this skill was developed and regression-tested against. Read it only if you need
to reproduce a figure quoted here.

**The three cookbook files are the deliverable of Step 5.** Each recipe carries the
real DAX, the Malloy, whether the Malloy was **executed** or only `semantics-cited`,
and what the port costs. Route with `translate-measures.md`, then transpile from the
recipe - do not hand the user a classification and call it a migration.

### Shared Reference

`reference/_concepts.md` is the Power BI to Malloy concept mapping table. Referenced by `propose-fields.md`, `translate-measures.md`, and `rls-roles.md` for type mapping and syntax translation.

### Script

`scripts/classify_measures.py` routes a whole model in one pass, so a migration starts
from an inventory rather than from measure one. It does the three things that do not
survive reading measures one at a time at a few thousand of them: the dependency graph,
return-type inference from the model's own column types, and the relationship flags that
never appear in a measure's DAX. It has a `--json` mode for the `.pbix` path, which has
no TMDL. It runs where you have a shell (Claude Code, Cursor); the Credible app's agent
has no shell tool and the MCP skills bundle ships markdown only, so the prose stands alone.

**DAX is not only in `measure` declarations, and the rest is not a rounding error.**
The script also reads calculation groups, `functions.tmdl`, calculated columns,
calculated-table partitions and `roles/*.tmdl`, because three recipes fire **zero**
times in any measure body and are not rare at all: `CALENDAR()` is only ever in a
calculated-table partition, calculation items carry a model's time intelligence, and RLS
predicates live in `roles/*.tmdl`. Match only `measure` and you will tell a customer
their model has no calculation groups, no date spine and no row-level security.
`reference/limitations.md` is the inventory of what it reads, what it does not, and the

From malloydata/publisher, MIT.

More from malloydata/publisher