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.

Column/Field Name Quoting (CRITICAL)¶

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

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)¶

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.