dataflows-authoring-cli

# 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

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

# 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

# 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.

# Skeleton — full PowerShell recipe: references/output-destinations.md § Complete Example
WS_ID=" "; LH_ID=" "; RES="https://api.fabric.microsoft.com"

# M pattern (two queries):
# 1. Source with [DataDestinations] annotation
# 2. Hidden _DataDestination query with ?[Data]? null-safe navigation
# queryMetadata: source loadEnabled=true, destination loadEnabled=false + isHidden=true
# Refresh: {"executionData":{"executeOption":"ApplyChangesIfNeeded"}}

# Prereqs: az login, jq, base64, uuidgen. Workspace must support Dataflow Gen2.
WS_ID=" "
DF_NAME="my-titanic-df"
CONN_NAME="my-titanic-web-conn"
URL="https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv"
RES="https://api.fabric.microsoft.com"; API="$RES/v1"
PBI="https://analysis.windows.net/powerbi/api" # NO trailing slash

# Step 1: List existing connections, try to reuse by name.
CONN_ID=$(az rest --method get --resource "$RES" --url "$API/connections" \
 --query "value[?displayName=='$CONN_NAME'] | [0].id" -o tsv)

# Step 2: Create if missing (Web + Anonymous; see connection-management.md for other shapes).
if [ -z "$CONN_ID" ] || [ "$CONN_ID" = "null" ]; then
 BODY_FILE=$(mktemp --suffix=.json 2>/dev/null || mktemp) # GNU + BSD/macOS compatible
 cat > "$BODY_FILE" /dev/null)
 [ -n "$CLUSTER_ID" ] && [ "$CLUSTER_ID" != "null" ] && break
 sleep $((i*3))
done
# Fail-fast: an empty ClusterId silently corrupts the composite connectionId and the
# resulting updateDefinition / refresh failures are hard to debug. Stop here instead.
if [ -z "$CLUSTER_ID" ] || [ "$CLUSTER_ID" = "null" ]; then
 echo "FAIL: ClusterId not resolved for $CONN_ID after retries. Verify the connection is visible at PBI v2 (api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources)." >&2
 exit 1
fi

# Step 4: Create empty dataflow shell (sync 201).
SHELL_BODY=$(mktemp --suffix=.json 2>/dev/null || mktemp)
printf '{"displayName":"%s"}' "$DF_NAME" > "$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"

# Step 5: One-shot updateDefinition — real M + composite-bound connections[] + .platform.
MASHUP='section Section1;
shared Titanic = let
 Source = Csv.Document(Web.Contents("'"$URL"'"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
 Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in Headers;'

COMPOSITE_ID="{\"ClusterId\":\"$CLUSTER_ID\",\"DatasourceId\":\"$CONN_ID\"}"
QUERY_META=$(jq -n --arg name "$DF_NAME" --arg cid "$COMPOSITE_ID" --arg url "$URL" --arg qid "$(uuidgen)" '{
 formatVersion: "202502",
 name: $name,
 queriesMetadata: { Titanic: { queryId: $qid, queryName: "Titanic" } },
 connections: [ { connectionId: $cid, kind: "Web", path: $url } ]
}')
PLATFORM=$(jq -n --arg name "$DF_NAME" --arg lid "$(uuidgen)" '{
 "$schema": "https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json",
 metadata: { type: "Dataflow", displayName: $name },
 config: { version: "2.0", logicalId: $lid }
}')

MASHUP_B64=$(echo -n "$MASHUP" | base64 -w0)
META_B64=$(echo -n "$QUERY_META" | base64 -w0)
PLAT_B64=$(echo -n "$PLATFORM" | base64 -w0)

BODY_FILE=$(mktemp --suffix=.json 2>/dev/null || mktemp) # GNU + BSD/macOS compatible
cat > "$BODY_FILE" &2; exit 1; }

# Step 7 (optional): Validate the M evaluates — top-level QueryName, PascalCase.
EQ_BODY=$(mktemp --suffix=.json 2>/dev/null || mktemp)
printf '{"QueryName":"Titanic"}' > "$EQ_BODY"
az rest --method post --resource "$RES" \
 --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/executeQuery" \
 --headers "Content-Type=application/json" \
 --body "@$EQ_BODY" --output-file /tmp/titanic.arrow
rm -f "$EQ_BODY"
# Apache Arrow stream — embedded {"Error":"..."} means failure even on HTTP 200.
grep -q '"Error":"' /tmp/titanic.arrow && { echo "executeQuery surfaced an error." >&2; exit 1; }

# Step 8 (optional): Trigger refresh with ApplyChangesIfNeeded on first run — see Example 2.

# Trigger refresh (returns 202 + Location header for polling).
# jobType MUST be "Refresh"; "Pipeline" returns 400 InvalidJobType.
# On the first refresh after any updateDefinition, body MUST include executeOption=ApplyChangesIfNeeded
# (otherwise Fabric refreshes the previously-applied definition).
# Acquire $TOKEN per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = https://api.fabric.microsoft.com).
LOCATION=$(curl -sS -X POST \
 -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
 --data '{"executionData":{"executeOption":"ApplyChangesIfNeeded"}}' \
 "https://api.fabric.microsoft.com/v1/workspaces/${WS_ID}/dataflows/${DF_ID}/jobs/instances?jobType=Refresh" \
 -o /dev/null -D - | tr -d '\r' | grep -i "^location:" | awk '{print $2}')

# Poll until terminal (Fabric refresh job status enum: NotStarted / InProgress / Completed / Failed / Cancelled).
while true; do
 STATUS=$(az rest --method get --url "$LOCATION" \
 --resource "https://api.fabric.microsoft.com" --query "status" -o tsv)
 echo "Status: $STATUS"
 [[ "$STATUS" == "Completed" || "$STATUS" == "Failed" || "$STATUS" == "Cancelled" ]] && break
 sleep 10
done

# Notes:
# - $Resp.Headers["Location"] returns string or string[] depending on PS version — never
# use .Location[0] (returns first character on Windows PS 5.1 plain-string case).
# - Wrap Invoke-WebRequest in try/catch on 5.1 (-SkipHttpErrorCheck is PS 7+).
# - Fabric refresh job status enum: NotStarted / InProgress / Completed / Failed / Cancelled.
# This is distinct from the LRO operation enum (Running / Succeeded / Failed / Cancelled).
# Refresh "success" = "Completed", not "Succeeded".
# Acquire $Token per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = https://api.fabric.microsoft.com).
try {
 $Resp = Invoke-WebRequest -Method POST -UseBasicParsing `
 -Uri "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/dataflows/$DF_ID/jobs/instances?jobType=Refresh" `
 -Headers @{ Authorization = "Bearer $Token"; "Content-Type" = "application/json" } `
 -Body '{"executionData":{"executeOption":"ApplyChangesIfNeeded"}}'
} catch {
 Write-Error "Refresh trigger failed: $($_.Exception.Message)"; exit 1
}
$Location = $Resp.Headers["Location"]
if ($Location -is [array]) { $Location = $Location[0] }

while ($true) {
 $Status = az rest --method get --url $Location `
 --resource "https://api.fabric.microsoft.com" --query "status" -o tsv
 Write-Host "Status: $Status"
 if ($Status -in 'Completed','Failed','Cancelled') { break }
 Start-Sleep -Seconds 10
}

RESOURCE="https://api.fabric.microsoft.com"
# Acquire $TOKEN per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = $RESOURCE).

# 1. Read current definition (sync 200 or 202 LRO — handle both).
HDR=$(mktemp); BODY=$(mktemp)
CODE=$(curl -sS -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
 "$RESOURCE/v1/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
 DEF=$(cat "$BODY")
fi
rm -f "$HDR" "$BODY"

# 2. Decode each part, modify mashup.pq, re-encode all 3.
MASHUP=$(echo "$DEF" | jq -r '.definition.parts[] | select(.path=="mashup.pq") | .payload' | base64 -d)
META=$( echo "$DEF" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d)
PLAT=$( echo "$DEF" | jq -r '.definition.parts[] | select(.path==".platform") | .payload' | base64 -d)

NEW_MASHUP=$(echo "$MASHUP" | sed 's/old-pattern/new-pattern/') # edit M here

MASHUP_B64=$(echo -n "$NEW_MASHUP" | base64 -w0)
META_B64=$(echo -n "$META" | base64 -w0)
PLAT_B64=$(echo -n "$PLAT" | base64 -w0)

# 3. Build the updateDefinition body in a temp file (full replacement — all 3 parts).
BODY_FILE=$(mktemp --suffix=.json 2>/dev/null || mktemp) # GNU + BSD/macOS compatible
cat > "$BODY_FILE" 
 Binding a new connection? Example 1 (steps 1-5) is the canonical bind+save flow. Bind-only walk-throughs live in [authoring-cli-quickref.md § Connection Binding Quick Patterns](https://github.com/microsoft/skills-for-fabric/blob/main/skills/dataflows-authoring-cli/references/authoring-cli-quickref.md#connection-binding-quick-patterns) and [authoring-script-templates.md § Connection Binding Templates](https://github.com/microsoft/skills-for-fabric/blob/main/skills/dataflows-authoring-cli/references/authoring-script-templates.md#connection-binding-templates).

## Output Expectations

When this skill completes a task, the agent should return:

 Field Convention 
 **Verbosity** Concise summary (3–10 lines) of what was created/modified. 
 **Default format** Markdown for status reports; fenced JSON code block for single-resource responses; markdown table for list responses. 
 **Side-effect disclosure** Explicitly report IDs created/modified/deleted and the target workspace ID. Never imply success without an ID. When you saved or replaced a dataflow definition, name the parts you wrote in prose — `mashup.pq`, `queryMetadata.json`, `.platform` — since long command bodies are truncated in the transcript and the part names would otherwise be lost. 
 **Verification** Re-`GET` the affected resource (dataflow, connection, job instance) and surface its state (e.g., `provisionState`, `status`, `Completed`) before declaring done. 
 **Error surfacing** If any step returned a non-2xx status, an LRO `Failed`/`Cancelled`, or an Arrow-stream `{"Error":"..."}`, propagate the raw error verbatim and stop. 
 **Preview rendering (Workflow C)** After `executeQuery`, render `head(10)` of the result as a markdown table in chat alongside the saved Arrow file — even when the embedded-error check passes. Catches silent-success bugs (filter dropped all rows, wrong column, off-by-one, wrong cast) that the embedded-error detector cannot see. Snippet + suppression rules: [dataflows-consumption-cli § Example 5b](https://github.com/microsoft/skills-for-fabric/blob/main/skills/dataflows-authoring-cli/../dataflows-consumption-cli/SKILL.md#example-5b-render-query-results-as-a-markdown-table). 
 **API names** When the answer references API endpoints or request-body fields, use their exact, case-sensitive names (`executeQuery`, `customMashupDocument`, `QueryName`, `mashup.pq`, `queryMetadata.json`, `GET /v1/connections/supportedConnectionTypes`) rather than paraphrased or pluralized variants.