dataflow-gen1-extractor

Dataflow Gen1 Extractor

Extract all Dataflow Gen1 definitions from a Power BI workspace and parse them into individual M query files with classification metadata.

Two-Step Process (plus optional Step 0 for discovery)

Step 0 (optional): Discover dataflows across all workspaces (PowerShell - user runs manually)

If you don't know the workspace ID yet, generate a tenant-wide discovery script:

python scripts/generate_discovery_script.py --output "<TARGET_DIR>/Discover-AllDataflows.ps1"

Optional flags:

• --csv-output <name> — Where the generated script writes its CSV (default: gen1-dataflow-inventory.csv next to the .ps1)
• --scope Organization — Default scope baked into the script. Individual (default) lists workspaces the user is a member of; Organization lists every workspace in the tenant (Power BI admin only).

The user runs the generated script manually in PowerShell (requires interactive browser auth):

pwsh -File "<TARGET_DIR>/Discover-AllDataflows.ps1"
# or to override scope at runtime:
pwsh -File "<TARGET_DIR>/Discover-AllDataflows.ps1" -Scope Organization

Output: A CSV with one row per Gen1 dataflow across every accessible workspace. Columns: workspace_name, workspace_id, workspace_type, workspace_capacity_id, dataflow_name, dataflow_id, modified_date, configured_by, description. Use the CSV to pick which workspace(s) to migrate, then proceed to Step 1 with the chosen workspace_id.

Step 1: Export (PowerShell - user runs manually)

Generate Export-AllDataflows.ps1 from the template, substituting the workspace ID:

python scripts/generate_export_script.py --workspace-id "<WORKSPACE_ID>" --output "<TARGET_DIR>/Export-AllDataflows.ps1"

The user must run this script manually in PowerShell (requires interactive browser auth via Connect-PowerBIServiceAccount).

Outputs: {json_dir}/*.json + {json_dir}/dataflow_manifest.csv

Step 2: Parse (Python - can run non-interactively)

Extract M queries from the exported JSON files:

python scripts/extract_m_from_json.py --source "<JSON_DIR>" --output "<OUTPUT_DIR>" --inventory "<INVENTORY_CSV_PATH>"

Outputs:

• {output_dir}/{dataflow_name}/*.pq (one .pq file per query)
• {inventory_csv} with columns: dataflow_name, query_name, file_path, line_count, starts_with_let, source_type, role, extraction_method

Parameters

Parameter	Step	Required	Description
--output	0	Yes	Path for generated Discover-AllDataflows.ps1
--csv-output	0	No	CSV path inside the generated script (default: gen1-dataflow-inventory.csv)
--scope	0	No	Individual (default) or Organization (admin)
--workspace-id	1	Yes	Power BI workspace GUID
--output	1	Yes	Path for generated Export-AllDataflows.ps1
--json-dir	1	No	Override JSON output directory in generated script (default: same as script location)
--source	2	Yes	Directory containing exported JSON files
--output	2	Yes	Directory for extracted .pq files
--inventory	2	No	Path for query_inventory.csv (default: {output}/query_inventory.csv)

JSON Format Details

See references/json-formats.md for the 4 known JSON structures from Export-PowerBIDataflow.

Query Classification

The parser auto-classifies each query:

Source types: sql_server, analysis_services, sharepoint, excel, csv, web, odata, azure_storage, linked_dataflow, static_table, json, derived

Roles: output_entity (loaded to storage), staging (referenced by others), transformation (no direct source), parameter_or_function, helper

Prerequisites

• PowerShell 5.1+ (Windows built-in) or PowerShell 7+ (cross-platform).
• MicrosoftPowerBIMgmt PowerShell module. Verify with Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt. If missing, install once per user (no admin rights needed):

  Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser
  

  Both Discover-AllDataflows.ps1 and Export-AllDataflows.ps1 check for this module on startup and emit a clear error + install command if it's missing.
• Python 3.10+ (standard library only — no pip installs needed).
• Workspace access — the user signing in must have Contributor or higher on every workspace they want to export. Discovery (Step 0) works on any workspace the user can see; export (Step 1) requires Contributor.