# excel_powerquery - Server Quirks
# excel_powerquery - Server Quirks
## BEST PRACTICE: Test-First Development Workflow
**ALWAYS evaluate M code before creating permanent queries:**
```
1. evaluate → Test M code without persisting (catches syntax errors, validates data sources)
2. create/update → Store validated query in workbook
3. refresh/load-to → Load data to destination
```
**Why evaluate first:**
- No permanent query created - test-then-commit approach
- Better error messages than COM exceptions
- See actual data preview (columns + sample rows)
- No cleanup needed - temporary objects auto-deleted
- Like a REPL for M code
**When to skip evaluate:** Only for trivial literal tables (`#table` with hardcoded values).
---
**Automatic M-Code Formatting**:
- Create and Update operations automatically format M code using powerqueryformatter.com API
- Formatting adds ~100-500ms network latency per call
- Graceful fallback: returns original M code if formatting service unavailable
- Read operations (List, View) return M code as stored (no formatting on read)
- Formatting improves readability with proper indentation, spacing, and line breaks
**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 excel_powerquery create/load-to with `loadDestination='data-model'`
2. Then use excel_datamodel to create DAX measures
Alternative path (for existing worksheet tables):
1. Use excel_table with `add-to-datamodel` action
2. Then use excel_datamodel to create DAX measures
**Action disambiguation**:
- **evaluate**: **CRITICAL - TEST FIRST** - Execute M code directly WITHOUT creating permanent query (catches errors before persisting!)
- 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
- **CRITICAL**: Always evaluate M code FIRST to validate before persisting
**RECOMMENDED WORKFLOW - Always evaluate before create**:
1. `evaluate` → verify data looks correct (catches syntax errors, missing sources, wrong columns)
2. `create` → stores validated query in workbook
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.
This avoids polluting the workbook with broken queries and gives better error messages than COM exceptions.
**Additional evaluate use cases**:
- Execute one-off queries without creating permanent queries
- Ad-hoc data exploration or debugging M code transformations
- Returns tabular data (columns, rows) in JSON - no cleanup needed
**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** → Creating/updating with untested M code (pollutes workbook, cryptic COM errors)
- 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 action REQUIRES `refreshTimeoutSeconds` between 60-600 seconds (1-10 minutes). If refresh needs more than 10 minutes, ask the user to run it manually in Excel—the server refuses longer windows and will not pick a default for you.
- load-to has a 5-minute guard. 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 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`
