# Power Query

## RECOMMENDED DEVELOPMENT WORKFLOW (ALWAYS USE THIS)

**Test BEFORE persisting - avoid polluting workbooks with broken queries:**

```
Step 1: evaluate → Test M code, verify results (catches syntax errors, missing sources)
Step 2: create/update → Store VALIDATED query in workbook
Step 3: refresh/load-to → Load data to destination (worksheet/data-model)
```

**Why this workflow:**
- `evaluate` executes M code WITHOUT creating permanent query (test-then-commit)
- Returns actual data preview with columns and rows in JSON
- Better error messages than COM exceptions from create/update
- No cleanup needed - temporary objects auto-deleted
- Skip evaluate only for trivial literal tables (`#table` with hardcoded values)

**IF CREATE/UPDATE FAILS**: Use `evaluate` to get detailed Power Query error message, fix code, retry.

**Additional evaluate use cases:**
- Execute one-off queries without creating permanent queries
- Ad-hoc data exploration or debugging M code transformations
- Quick testing during development (like REPL for M code)

---

**M-Code Formatting**:

- Create and Update preserve M code exactly by default and do not call remote services
- Set `formatMCode=true` only with explicit user consent; it sends M code to powerqueryformatter.com
- Remote formatting adds ~100-500ms network latency per call
- Graceful fallback: saves original M code if the formatting service is unavailable
- Read operations (List, View) return M code as stored (no formatting on read)

**Data Model workflow**:

Power Query can load data to different destinations:
- `worksheet` (default): Creates an Excel Table on a worksheet
- `data-model`: Loads directly to Power Pivot for DAX analysis
- `both`: Loads to worksheet AND Power Pivot
- `connection-only`: Imports query definition without loading data

To create DAX measures on Power Query data:
1. Use powerquery create/load-to with `loadDestination='data-model'`
2. Then use datamodel to create DAX measures

Alternative path (for existing worksheet tables):
1. Use table with `add-to-data-model` action
2. Then use datamodel to create DAX measures

**Action disambiguation**:

- **evaluate**: **CRITICAL - USE THIS FIRST** - Execute M code directly, return results WITHOUT creating a permanent query (test before create/update!)
- create: Import NEW query using inline `mCode` (FAILS if query already exists - use update instead)
- update: Update EXISTING query M code + refresh data (use this if query exists)
- rename: Change query name (requires both `queryName` and `newName` parameters)
- load-to: Loads to worksheet or data model or both (not just config change) - CHECKS for sheet conflicts
- unload: Removes data from ALL destinations (worksheet AND Data Model) - keeps query definition
- delete: Completely removes query AND all associated data (worksheet, Data Model connections)

**Rename behavior**:

- Names are trimmed and compared case-insensitively for uniqueness
- Renaming "Query1" to "query1" is allowed (case-only change, no conflict)
- Renaming "Query1" to " Query1 " is a no-op (trimmed names match)
- No-op (same normalized name) → success with `oldName` = `newName`
- Conflict with existing query → error with `errorMessage`
- M code content is unchanged - only the name changes
- No auto-save: workbook must be saved separately to persist the rename

**When to use create vs update**:

- Query doesn't exist? → Use create
- Query already exists? → Use update (create will error "already exists")
- Not sure? → Check with list action first, then use update if exists or create if new
- **ALWAYS evaluate M code FIRST** to catch errors before persisting

**List action and IsConnectionOnly**:

- `IsConnectionOnly=true` means query has NO data destination (not in worksheet, not in Data Model)
- `IsConnectionOnly=false` means query loads data SOMEWHERE (worksheet OR Data Model OR both)
- A query loaded ONLY to Data Model is NOT connection-only

**Inline M code**:

- Provide raw M code directly via `mCode`
- Keep `.pq` files only for GIT workflows

**Create/LoadTo with existing sheets**:

- Use `targetCellAddress` to place the table on an existing worksheet without deleting other content
- Applies to BOTH create and load-to
- If the worksheet already has data and you omit `targetCellAddress`, the tool returns guidance telling you to provide one
- Existing tables are refreshed in-place; specifying a different `targetCellAddress` requires unload + reload
- Worksheets that exist but are empty behave like new sheets (default destination = A1)

**Common mistakes**:

- **WARNING: Skipping evaluate** → Create/update with untested M code (ERROR: pollutes workbook with broken queries)
- Using create on existing query → ERROR "Query 'X' already exists" (should use update)
- Using update on new query → ERROR "Query 'X' not found" (should use create)
- Calling LoadTo without checking if sheet exists (will error if sheet exists)
- Assuming unload only removes worksheet data → Also removes Data Model connections
- Calling rename without trimming newName → Server trims automatically, " Query " becomes "Query"
- Renaming to conflicting name → Check list first if unsure about existing names

**Server-specific quirks**:

- Validation = execution: M code only validated when data loads/refreshes
- connection-only queries: NOT validated until first execution
- refresh with loadDestination: Applies load config + refreshes (2-in-1)
- Single cell returns [[value]] not scalar
- refresh defaults to 30-minute timeout if `refreshTimeoutSeconds` is 0 or omitted. Any positive value is accepted. For quick queries use a smaller value (e.g., 60-120 seconds).
- load-to uses the same 30-minute timeout as refresh. If Excel is blocked by privacy dialogs/credentials, you'll get `SuggestedNextActions` instead of a hang—surface them to the user before retrying.

**Data Model connection cleanup**:

- Unload removes BOTH worksheet ListObjects AND Data Model connections
- Delete removes query, worksheet ListObjects, AND Data Model connections
- Connection naming pattern: "Query - {queryName}" or "Query - {queryName} - suffix"

## M Code - Server-Specific Notes

> For full M code language syntax, see [m-code-syntax reference](/reference/m-code-syntax/).

### Column/Field Name Quoting (CRITICAL)

M code requires special syntax for identifiers containing hyphens, spaces, or special characters:

| Column Name | Syntax | Notes |
|-------------|--------|-------|
| `Amount` | `[Amount]` | Simple names work without quotes |
| `Non-Recurring` | `[#"Non-Recurring"]` | **Hyphen requires `#"..."` quoting** |
| `List Price (USD)` | `[#"List Price (USD)"]` | Spaces/parens require quoting |
| `Service Level 1` | `[#"Service Level 1"]` | Spaces require quoting |

**Common mistake:** `[Non-Recurring]` parses as `[Non] - [Recurring]` (subtraction!) and fails with cryptic "The name 'X' wasn't recognized" errors.

**Rule:** If a column name contains anything other than letters, numbers, and underscores, use `[#"Column Name"]` syntax.

### Reading Named Ranges (parameters)

```m
Excel.CurrentWorkbook(){[Name = "Param_Name"]}[Content]{0}[Column1]
```

### Query Chaining

Reference other queries by name directly: `Source = OtherQueryName`

### Source Control Pattern

1. Store M code in `.pq` files
2. `powerquery create` or `update` with inline `mCode`
3. `refresh` to validate
4. File name MUST match query name

Query naming: File name MUST match Excel query name exactly.