Dataflows Authoring Cli

> Update Check — ONCE PER SESSION (mandatory) > The first time this skill is used in a session, run the check-updates skill before proceeding. > - GitHub Copilot CLI / VS Code: invoke the check-updates skill. > - Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version. > - Skip if the check was already performed earlier in this session.

> CRITICAL NOTES > 1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering > 2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

dataflows-authoring-cli — Dataflows Gen2 Authoring via CLI

Table of Contents

This skill (SKILL.md)

| Section | Notes | |---|---| | [Tool Stack](#tool-stack) | az + jq + base64 + curl | | [Connection](#connection) | Workspace/dataflow ID discovery | | [Agentic Workflows](#agentic-workflows) | Start here. A: create end-to-end; B: modify existing; C: preview loop | | [MUST DO / AVOID / PREFER](#must-do) | Authoring rules | | [Troubleshooting](#troubleshooting) | Symptom → fix table | | [Examples](#examples) | Runnable bash + PowerShell recipes | | [Output Expectations](#output-expectations) | Response conventions |

References (in [references/](references/))

| File | When to read | |---|---| | [authoring-cli-quickref.md](references/authoring-cli-quickref.md) | One-liner recipes, status enums, base64 helpers, connection-binding quick patterns | | [authoring-script-templates.md](references/authoring-script-templates.md) | Full bash + PowerShell templates; end-to-end smoke test; LRO polling pattern | | [connection-management.md](references/connection-management.md) | List/create/inspect connections; supportedConnectionTypes; resolve ClusterId; ID format cheat sheet | | [connectors.md](references/connectors.md) | M-side source connectors: live-verified function inventory, Lakehouse deep navigation, runtime-disabled functions (Web.Page, Web.BrowserContents), Html.Table / Csv.Document / Json.Document patterns | | [m-language.md](references/m-language.md) | M language semantics for Dataflow Gen2: try record shapes, per-cell error wrapping in column transforms, each scoping in row vs sub-table contexts, optional field access [?] / Record.FieldOrDefault, quoted identifiers, sandbox-disabled symbols (File.Contents) | | [mashup-preview.md](references/mashup-preview.md) | executeQuery contract: bootstrap branch, auto-wrap rule, hard avoid for unbounded preview | | [output-destinations.md](references/output-destinations.md) | Output destination patterns: Lakehouse Table, Lakehouse Files, Warehouse, ADX, Azure SQL. DataDestinations annotation, hidden query, loadEnabled rules, connection limitations |

Common refs (in [../../common/](../../common/))

| File | When to read | |---|---| | [COMMON-CLI.md](../../common/COMMON-CLI.md) | az login, token acquisition, az rest, pagination, LRO polling, CLI gotchas. § Finding Workspaces and Items in Fabric is mandatory. | | [COMMON-CORE.md](../../common/COMMON-CORE.md) | Fabric topology, environment URLs, authentication, core REST API surface | | [ITEM-DEFINITIONS-CORE.md](../../common/ITEM-DEFINITIONS-CORE.md) | Definition envelope; per-item-type payload contracts | | [DATAFLOWS-AUTHORING-CORE.md](../../common/DATAFLOWS-AUTHORING-CORE.md) | Authoring capability matrix; 3-part definition structure; M structure; connection model; ALM / Git integration |

Sister skills

| Skill | Use for | |---|---| | [dataflows-consumption-cli](../dataflows-consumption-cli/SKILL.md) | Execute persisted queries; ad-hoc read-only customMashupDocument with no intent to persist; Arrow → CSV/pandas conversion; refresh status/history. |

---

Tool Stack

| Tool | Role | Install | |---|---|---| | az CLI | Primary: Auth (az login), REST API calls (az rest), token acquisition. | Pre-installed in most dev environments | | jq | Parse and manipulate JSON responses and definition payloads. | Pre-installed or trivial | | base64 | Encode/decode definition parts for the REST API. | Built into bash / [Convert]::ToBase64String() in PowerShell | | curl | Alternative to az rest when raw HTTP control is needed. | Pre-installed | | uuidgen | Generate per-query / per-platform GUIDs for queryId and logicalId when building a new dataflow definition (Workflow A). | Pre-installed on Linux/macOS; on Windows use PowerShell [guid]::NewGuid().Guid or run via WSL |

> Agent check — verify az, jq, and curl are available before first operation. uuidgen is only needed for Workflow A (Create). > For installation and auth setup see [COMMON-CLI.md](../../common/COMMON-CLI.md).

---

Connection

Discover Workspace and Dataflow IDs

Per [COMMON-CLI.md](../../common/COMMON-CLI.md) Finding Workspaces and Items in Fabric:

# List workspaces — find workspace ID by name
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces" \
  --query "value[?displayName=='MyWorkspace'].id" --output tsv

# List dataflows in workspace — find dataflow ID by name
WS_ID=""
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/dataflows" \
  --query "value[?displayName=='MyDataflow'].id" --output tsv

Reusable Connection Variables

WS_ID=""
DF_ID=""
API="https://api.fabric.microsoft.com/v1"
RESOURCE="https://api.fabric.microsoft.com"

---

Agentic Workflows

Three workflows cover the typical authoring tasks:

• [A. Create a New Dataflow End-to-End](#a-create-a-new-dataflow-end-to-end) — discover/create a connection, create the dataflow, save M + bindings, validate, optionally refresh.
• [B. Modify an Existing Dataflow](#b-modify-an-existing-dataflow) — read-modify-write the definition; the canonical Discover → Formulate → Execute → Verify loop.
• [C. Preview-Driven Authoring Loop](#c-preview-driven-authoring-loop) — iterate on candidate M via executeQuery before persisting via updateDefinition.
• [D. Output Destination](#d-output-destination) — write query results to Lakehouse (table/files), Warehouse, ADX, or Azure SQL via DataDestinations annotation. Full reference: [output-destinations.md](references/output-destinations.md).

A. Create a New Dataflow End-to-End

Use this when the dataflow does not yet exist. Covers the full happy path: discover-or-create a connection, create the dataflow shell, save M + bindings in one updateDefinition, validate, optionally refresh.

Steps:

1. List existing connections and filter by connectionDetails.type and the target URL/host — reuse if a match exists (GET /v1/connections + JMESPath).
2. If no match, create the connection. First GET /v1/connections/supportedConnectionTypes to discover required parameters and supported credential types, then POST /v1/connections (sync 201). Body shape and credential schemas: [connection-management.md](references/connection-management.md).
3. Resolve ClusterId for the composite binding. GET https://api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources with --query "value[?id=='$CONN_ID'] | [0].clusterId", audience --resource "https://analysis.windows.net/powerbi/api" (no trailing slash). The per-id route returns PowerBIEntityNotFound for cloud connections. Newly-created connections may take a few seconds to surface — retry on empty. Detail: [connection-management.md § Resolving ClusterId](references/connection-management.md#resolving-clusterid-power-bi-v2).
4. Create the dataflow shell. POST /v1/workspaces/{ws}/dataflows with {"displayName":"…"} returns sync 201. The definition field is optional at create time and can be set in the next step.
5. Save M + connection bindings in one call. POST /v1/workspaces/{ws}/dataflows/{df}/updateDefinition?updateMetadata=true with three parts: mashup.pq (real Web.Contents / Sql.Database / …), queryMetadata.json (with connections[] populated; each connectionId is the stringified composite {"ClusterId":"…","DatasourceId":"…"}), and .platform. Typically returns sync 200; may return 202 + LRO Location on large bodies — handle both.
6. Verify the binding persisted. Re-call getDefinition, decode queryMetadata.json, and confirm connections[] is intact. Do not use GET /items/{id}/connections for verification — that endpoint reflects refresh-materialized state, not the persisted definition, and returns 0 even after a successful bind. See [AVOID](#avoid).
7. (Optional) Validate via executeQuery before refresh. POST /v1/workspaces/{ws}/dataflows/{df}/executeQuery with body {"QueryName":""} (top-level, PascalCase QueryName). See [Workflow C](#c-preview-driven-authoring-loop).
8. (Optional) Trigger refresh to materialize. POST .../jobs/instances?jobType=Refresh with body {"executionData":{"executeOption":"ApplyChangesIfNeeded"}}. ApplyChangesIfNeeded is required on the first refresh after any definition change — without it, Fabric refreshes the previously-applied definition. Poll the LRO until status is Completed (refresh enum) or Failed/Cancelled.

# Concise skeleton — full runnable bash is Example 1 below.
# PowerShell + LRO-polled variants: references/authoring-script-templates.md

WS_ID=""; URL=""
RES="https://api.fabric.microsoft.com"; API="$RES/v1"
PBI="https://analysis.windows.net/powerbi/api"

# 1. List existing & try reuse
CONN_ID=$(az rest --method get --resource "$RES" --url "$API/connections" \
  --query "value[?connectionDetails.type=='Web' && connectionDetails.path=='$URL'] | [0].id" -o tsv)

# 2. Create connection if missing — see connection-management.md for full body
# 3. List+filter for ClusterId
CLUSTER_ID=$(az rest --method get --resource "$PBI" \
  --url "https://api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources" \
  --query "value[?id=='$CONN_ID'] | [0].clusterId" -o tsv)

# 4. Empty dataflow shell — sync 201
SHELL_BODY=$(mktemp --suffix=.json 2>/dev/null || mktemp)
printf '{"displayName":"my-df"}' > "$SHELL_BODY"
DF_ID=$(az rest --method post --resource "$RES" \
  --url "$API/workspaces/$WS_ID/dataflows" \
  --headers "Content-Type=application/json" \
  --body "@$SHELL_BODY" --query id -o tsv)
rm -f "$SHELL_BODY"

# 5. One-shot updateDefinition with real M + connections[] (sync 200 typical)
#    Body assembly (mashup.pq + queryMetadata.json + .platform, base64-encoded;
#    queryMetadata.json.connections[].connectionId = composite ClusterId/DatasourceId):
#    see Example 1 below.

# 6. Verify via getDefinition (NOT GET /items/{id}/connections — see AVOID)
# 7. (optional) executeQuery — Workflow C
# 8. (optional) Refresh with executeOption=ApplyChangesIfNeeded — Example 2

> One-shot vs two-step bind+save. Steps 4-5 can be one call (default; saves an HTTP round trip) or split into a bootstrap-bind updateDefinition followed by a full-M updateDefinition. Both work — see [PREFER](#prefer).

B. Modify an Existing Dataflow

Use this when the dataflow already exists. Canonical Discover → Formulate → Execute → Verify loop. If the dataflow does not yet exist, see [Workflow A](#a-create-a-new-dataflow-end-to-end) instead.

1. Discover — list workspaces, list dataflows, getDefinition (decode mashup.pq and queryMetadata.json). Validate all connections[] entries via GET /v1/connections/{id}.
2. Formulate — modify M, re-encode parts, ensure every referenced connectionId exists in the caller's connection store.
3. Execute — POST .../updateDefinition?updateMetadata=true with all 3 parts (full replacement). Optionally trigger refresh.
4. Verify — re-call getDefinition to confirm changes; poll refresh LRO; for refresh failures, isolate M+source via executeQuery before re-triggering.

# Concise skeleton — full templates: references/authoring-script-templates.md
# Acquire $TOKEN per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = $RESOURCE).
RESOURCE="https://api.fabric.microsoft.com"; API="$RESOURCE/v1"

# 1. Discover — getDefinition (handles 200 sync and 202 + LRO via curl)
HDR=$(mktemp); BODY=$(mktemp)
CODE=$(curl -sS -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
  "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition" \
  -D "$HDR" -o "$BODY" -w "%{http_code}")
if [ "$CODE" = "202" ]; then
  LOC=$(tr -d '\r' &2; exit 1 ;;
    esac
  done
else
  RESULT=$(cat "$BODY")
fi
rm -f "$HDR" "$BODY"

# Validate bound connections (connectionId is a composite JSON string — iterate safely)
QUERY_META=$(echo "$RESULT" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d)
echo "$QUERY_META" | jq -c '.connections[]?' | while IFS= read -r conn; do
  RAW=$(echo "$conn" | jq -r '.connectionId')
  DATASOURCE_ID=$(echo "$RAW" | jq -r '.DatasourceId? // empty' 2>/dev/null)
  [ -z "$DATASOURCE_ID" ] && DATASOURCE_ID="$RAW"
  # GET /v1/connections/$DATASOURCE_ID to confirm access
done

# 2-3. Formulate & Execute — see Example 3
# 4. Verify — trigger refresh via curl (az rest cannot capture Location header).
#    Full LRO polling: references/authoring-script-templates.md.

C. Preview-Driven Authoring Loop (pre-save executeQuery — see [mashup-preview.md](references/mashup-preview.md))

When the change touches Power Query M (new query, edited mashup, new source, changed parameters), preview the candidate customMashupDocument against the dataflow's bound connections before persisting. Catches syntax, schema, and credential errors at authoring time. Full prerequisites, bootstrap branch, auto-wrap rule, hard-avoid for unbounded preview, and Apache Arrow handling: [mashup-preview.md](references/mashup-preview.md).

> Intent split. This workflow is for the pre-save intent. To execute a saved query (QueryName only) or run an ad-hoc read-only customMashupDocument with no intent to persist, use [dataflows-consumption-cli](../dataflows-consumption-cli/SKILL.md#query-evaluation). mashup-preview.md is the shared API reference for both intents.

Minimal ordered steps:

1. Locate or create the dataflow shell — POST /v1/workspaces/{ws}/dataflows with {"displayName":"…"} (workflow A step 4).
2. Ensure connections are bound — for new credentialed sources, do a minimal updateDefinition with queryMetadata.json connections[] first (the "bootstrap save"). A connections[] array declared only in the initial create payload is not yet visible to executeQuery.
3. Compose the candidate customMashupDocument as a complete section Section1; ... document. The request's QueryName (top-level, PascalCase) must match a shared member in the document.
4. Preview — POST /v1/workspaces/{ws}/dataflows/{df}/executeQuery with body {"QueryName": "", "customMashupDocument": ""}. Pass --output-file results.arrow — az rest writes the raw Apache Arrow IPC stream to disk. Arrow → CSV/pandas: [dataflows-consumption-cli § Query Evaluation](../dataflows-consumption-cli/SKILL.md#query-evaluation).
5. Validate the preview (two-tier — both required before persisting):

• a. Embedded-error check. HTTP 200 is not proof of success; engine errors are embedded inside the stream as {"Error":"..."}. Quick scan: grep -q '"Error":"' results.arrow. Canonical pyarrow detector inspects schema metadata — see [mashup-preview.md § Error handling — A](references/mashup-preview.md#detecting-failures-inside-the-arrow-body).
• b. Render head(10) as a markdown table to the user. The embedded-error check only catches engine-level failures (column not found, cast errors, SEM0100, etc.). It does not catch *silent-succ

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: microsoft
• Source: microsoft/skills-for-fabric
• License: MIT

Install and usage instructions live in the source repository linked above.