Power BI Dataflow Gen1 vs Gen2: Caveats Before You Migrate
Introduction
Thinking about moving a Power BI Dataflow Gen1 to Fabric Dataflow Gen2? The Power Query steps may look familiar, but the operating model changes: where data lands, which connector consumers use, who can read the output, and how a failed refresh affects the report. This guide walks you through a safe pilot before you change a production reporting chain.
Scope: Power BI Dataflows Gen1 and Microsoft Fabric Dataflow Gen2, not Azure Data Lake Storage Gen1 versus Gen2. Documentation checked on 11 October 2026. The example is illustrative; no customer migration or performance benchmark was executed.
Why This Matters
A report can still display yesterday’s imported data even when its next refresh will fail. That makes a migration look successful before anyone has tested the complete chain. Your acceptance criteria should cover source access, transformation output, destination writes, semantic model refresh, and the report viewed by its intended audience.
Treat the pilot as a reporting change. Ask who owns the source, whether historical rows can be reloaded, and which workbooks or models depend on the dataflow. Preserve a working route until the replacement passes those checks.
Gen1 vs Gen2: The Practical Differences
Both generations use Power Query to prepare data. Gen2 adds configurable destinations, pipeline integration, and Fabric monitoring. Since April 2026, new Gen2 items are created with CI/CD and Git integration support by default; the older Gen2 Classic creation path is no longer available. Existing classic items continue to work. Gen1 is now described as legacy and gets no new feature investment, but Microsoft says existing workloads remain supported while teams plan a transition.
Decision area | Gen1 | Gen2 caveat |
Workspace | Power BI Pro, PPU, or capacity scenarios | Fabric capacity and tenant permission are needed for the current Save As and Upgrade Wizard paths; Pro/PPU workspaces alone can't use them. |
Power Query | Existing transformations and queries | Reuse logic, then validate connections and outputs |
Output | Dataflow tables; storage options vary | Dataflow connector or explicitly configured destinations |
Incremental refresh | Premium features apply | Reconfigure it for a supported destination; Gen1 settings don't carry over. |
Migration route | Existing production item | Save As copy or preview in-place Upgrade Wizard |
Operations | Existing schedules and API automation | Review refresh order and Fabric capacity consumption |
Before You Start
Save As is unavailable for Power BI Pro and Premium Per User workspace types, and it can also be disabled by a tenant, capacity, or user-group setting. Check that Fabric items are enabled in the target workspace and confirm the permissions you need there. A personal Pro or PPU license by itself doesn't make the migration option appear.
A working Gen1 refresh and a named technical owner.
A test destination, plus permission to read the source and write the output.
A copy of the dependent Power BI Desktop report or Excel workbook.
Baseline checks: row count, key uniqueness, date range, business totals, and refresh duration.
A written cutover and fallback plan, including recoverability of historical data.
Step-by-Step Solution: Build a Controlled Pilot
Step 1: Inventory the Consumers
List every semantic model, workbook, downstream dataflow, scheduled job, and API-based process that reads the candidate. Record its owner and connection path. Include local PBIX and XLSX files; a workspace-only inventory is not a complete inventory of business usage.
Choose one small but representative table first. A useful pilot contains real transformation logic and at least one downstream consumer, while remaining simple enough that you can explain every reconciliation difference.
Step 2: Use Save As to Retain Gen1
In the workspace, open the dataflow’s ellipsis menu and select Save as Dataflow Gen2. Give the copy a clear pilot name and select Create. Review the new item before selecting Save or Save and run. Alternatively, Microsoft documents Power Query template export/import and copying selected queries.
Save As creates a separate Gen2 copy, so the Gen1 remains available for comparison. It doesn't copy scheduled refresh settings, and Gen1 incremental refresh settings aren't copied either. Record both configurations before you start, then rebuild and test them in the copy.
Step 3: Check Connections and the Reporting Route
Open each source connection, confirm credentials, and verify gateway selection where required. A successful editor preview is only an early check; test a saved refresh with the credentials and access that the service will actually use.
Gen2 tables can be consumed through the modern Power Platform Dataflows connector. Destinations are recommended for an explicit governed output, but are not universally mandatory. Do not connect reporting queries directly to the internal DataflowsStagingLakehouse: staging is a cache rather than your durable business data store.
For this pilot, write to a dedicated Lakehouse table and connect a copied report to that output. Keep the production report on its original route while you compare the two. This is a recommended testing arrangement, not a prerequisite for every Gen2 design.
Step 4: Decide How the Destination Updates
Inspect the destination settings before the first write. Automatic settings for supported new-table destinations use replace and can drop and recreate the table. Manual settings let you review mapping and schema behavior. Fixed schema protects the expected structure, but query changes that no longer match it require attention.
Append adds the query output on each refresh; it is not an automatic upsert. Re-reading the same rows and appending them can duplicate transactions. For a complete snapshot, start with replace in the test table. Choose append only when your input and repeat-run handling justify it.
Step 5: Rebuild Incremental Refresh Deliberately
Gen2 supports incremental refresh for Lakehouse, Warehouse, and Azure SQL Database destinations. Configure the destination explicitly in query settings. The documented requirements include fixed schema and the replace update method. Changed buckets are replaced; data outside the bucket range is left untouched.
Choose a date column for filtering and an appropriate change-detection column. Keep the full-folding requirement enabled unless you have assessed the consequences. Query folding means pushing processing to the source. Current limits are 50 buckets per query and 150 per dataflow.
Test late corrections and deletes explicitly. A change-detection rule based on a maximum value is not a guarantee that every business correction will trigger reprocessing. For Lakehouse incremental tables, also review Microsoft’s restrictions around overlapping initial data, other writers, and table maintenance.
Step 6: Reconcile Before Scheduling
Run the copied dataflow, inspect its destination, then refresh the copied semantic model. Compare results against the same source snapshot. Otherwise, new transactions arriving between the two runs can create differences that have nothing to do with migration.
Match row counts and transaction keys.
Compare totals by period and business unit, not just one grand total.
Check dates, decimal values, nulls, renamed columns, and relationships.
Run again without changing the source and check for unintended duplicates.
Test a controlled source correction and a failure path.
Confirm the report opens with the intended user’s permissions.
For an Import model, arrange ingestion before model refresh. Trigger the next step after successful completion, and decide what should happen when ingestion fails. Avoid a timetable that assumes every run finishes in the same number of minutes.
Practical Example: A Sales Snapshot
Suppose a controller receives three sales transactions from SQL. The existing Gen1 dataflow cleans the data and supplies an Import model. The pilot uses the same source snapshot, writes to a new Lakehouse table, and refreshes a copied report. These are invented sample rows for explaining the checks.
SaleID | SiteID | SaleDate | NetAmount |
1001 | 101 | 2026-10-01 | 120.00 |
1002 | 101 | 2026-10-02 | 80.00 |
1003 | 102 | 2026-10-02 | 150.00 |
The expected starting result is three rows, three unique SaleID values, and a total of 350.00: 200.00 for site 101 and 150.00 for site 102. Run the complete snapshot twice with replace; those results should remain unchanged.
Now correct transaction 1002 from 80.00 to 95.00 in the test source. After the next completed ingestion and Import-model refresh, the expected total is 365.00. If you append the full original snapshot twice, you instead get six rows and 700.00. That arithmetic exposes a load-design error before it reaches management reporting.
Bonus Tips and Common Mistakes
The Preview Wizard Is a Different Migration Route
The preview Upgrade Wizard is a different route: it replaces the Gen1 item in place while keeping the same ID, name, schedule, queries, and connections. The change can't be reversed. The cached historical rows aren't carried over, so the new item has no data until its first refresh completes. Back up history you can't reload from the source before you start.
The wizard assesses the dataflow itself, not every downstream consumer. It flags more than 50 enabled queries as a blocker; it can also flag unresolved connections, ownership, incremental refresh, Bring Your Own Lake storage, linked entities, and possible DirectQuery consumers. After the upgrade, refresh the item, open and save downstream dataflows so they rebind, then handle the remaining warnings. A same-ID migration reduces remapping, but it doesn't test permissions or refreshes for you.
DirectQuery: Official Guidance Currently Conflicts
The DirectQuery guidance is currently inconsistent. Microsoft's migration comparison and Upgrade Wizard pages say DirectQuery isn't supported for Gen2, while the Power Query Dataflows connector page—updated 9 October 2026—says DirectQuery from Power BI semantic models is supported on Fabric. The pages don't explain the difference. If DirectQuery is a hard requirement, test the exact connector, workspace, and Gen2 item with a copied semantic model before you choose a migration route.
Performance and Cost Need Separate Checks
Gen2 consumption can include standard compute, high-scale compute, and Fast Copy. Parallel execution can shorten elapsed time while still consuming compute across multiple workers. Use refresh history and the Fabric Capacity Metrics app together; report duration and CU consumption separately.
Optimize source queries and preserve folding where possible. Test staging changes against your actual transformations and data volume. Fast Copy eligibility depends on the workload; the Gen2 label alone does not establish that it will be used.
Troubleshooting
Symptom | Check first |
Next refresh fails after migration | Consumer connector, item IDs, credentials, and selected table |
Totals increase on an unchanged rerun | Append behavior and repeated source rows |
Publish fails after a column change | Destination mapping and fixed-schema agreement |
Gateway authentication fails on a long run | Supported gateway version and documented OAuth2 token limitation |
Many loaded queries fail validation | 50-query limit for queries staged or assigned destinations |
Author can connect, Viewer cannot | Gen2 connector consumption permissions; destination access is a separate route |
Microsoft currently requires Admin, Member, or Contributor for consuming Gen2 through the dataflow connector; Viewer is insufficient. Do not broaden workspace permissions automatically. Review whether destination-based access better fits the intended audience.
Connection checklist: what to verify before and after migration
A migration has several separate connections: the source into Gen2, Gen2 into its destination (if configured), the semantic model into Gen2 or that destination, and the report into the semantic model. Check each boundary. A green refresh at the first boundary is not an end-to-end test.
Connection boundary | Where to check | What must be verified |
Source → Dataflow Gen2 | Gen2 Power Query: each source connection; Service Settings → Manage connections and gateways | Correct source address, authentication, connection permissions, and gateway cluster where required. |
Gen2 → output table (optional) | Each output query: Data destination settings | Correct destination connection, write permissions, item/database, schema, table, column mapping, and update method. |
Gen2/output → semantic model | Desktop Transform data → affected query → Source and Navigation; then Service semantic model Settings | Correct connector, workspace/item IDs, table selection, sign-in, and Service connection mapping. |
Semantic model → reports | Report pages in Desktop and Service; shared-model reports as well | Correct model binding, relationships, measures, slicers, totals, freshness, and RLS results. |
1. Build an inventory at table and semantic-model level
Use workspace Lineage view as your starting point. For every dependent semantic model (formerly called a dataset), record its owner, PBIX location, affected Power Query queries/tables, connector, workspace ID, dataflow ID, and entity name. Include Excel workbooks and downstream dataflows. Local files are outside the Upgrade Wizard's assessment, so a Ready to migrate label does not clear them.
Record reports against their shared semantic model. Five reports can depend on one model: fix that model's affected source queries once, then validate all five reports. A thin report connected to a shared model has no local ingestion queries to rewrite; test its model connection and visuals instead. Keep a copy of the original PBIX and a baseline of row counts, latest dates, and business totals.
2. Check gateway connection access: Manage users
In the Power BI/Fabric Service, select Settings → Manage connections and gateways → Connections. Select the actual source connection → Manage users. Add the author or operational account/security group that needs to use this connection, choose the appropriate role (normally User for connection use), and select Share. Repeat for every required connection; one connection's user list does not cover the others.
Connection access, workspace access, gateway administration, and database permissions are different. Workspace membership alone does not grant connection access. Routine connection users do not need to become gateway administrators. Verify the stored authentication credentials also have the required source permissions; sharing the connection does not repair an expired password or inaccessible database.
For on-premises/private sources that require a gateway, check that the intended standard-mode or VNet gateway is available and that Gen2 uses the correct connection. For SQL Server bindings, compare the server and database values, including aliases, IP addresses, and instance names. If a query still uses an obsolete connection, select the correct one or recreate/rebind it. An unresolved source connection blocks the wizard.
Gen2's refresh guidance states that if one query uses a gateway, all queries in that dataflow use it for data movement. Check the reachability of every source in a mixed-source dataflow. A public cloud-only source does not automatically require an on-premises gateway just because you migrated to Gen2.
3. Verify source and destination connections separately
Open the Gen2 editor and check each query's source connection. If you configured a Lakehouse, Warehouse, or SQL destination, separately check its connection and write permissions, target item/database, schema/table, and column mapping. Destination settings are per query; validating Sales does not validate Customers. Review replace/append and schema settings against your intended loading behavior.
A model that reads a cloud Lakehouse/Warehouse output is not necessarily using the original SQL gateway itself: that gateway may belong only to Gen2's ingestion step. Check the actual model source rather than assigning the old gateway to every consumer. If a model also reads on-premises SQL directly, validate that separate Service binding.
4. Repair only the affected Desktop queries
Open the authoring PBIX in Power BI Desktop → Home → Transform data. Inspect Source and Navigation in each affected query, using Advanced Editor when needed. The legacy PowerBI.Dataflows connector cannot read upgraded Gen2 CI/CD items. Replace that access path with the modern Power Platform Dataflows connector (PowerPlatform.Dataflows), including its Workspaces navigation step, and sign in again with your organizational account. Changing only the function name can leave navigation incorrect.
A practical approach is to use Get data to create a temporary query with the modern connector, select the real workspace/dataflow/table, and compare its generated Source/Navigation steps with your original query. Transfer the required access steps while retaining the original query name and downstream transformations. Check navigation keys against the actual navigator; do not paste another item's IDs.
With an in-place wizard upgrade, the dataflow ID is preserved. With Save As or a rebuilt flow, update the dataflow ID and any changed workspace ID. If you instead switch to a Lakehouse/Warehouse destination, select the corresponding destination connector and table. Do not connect reports directly to internal DataflowsStagingLakehouse storage.
Inspect affected queries individually. Some may use the legacy connector, while others already use the modern connector, reference another query, or read an unchanged source. Changing every table automatically can create extra breakage. Connector/storage-mode limitations still need separate review for DirectQuery consumers.
5. Run a real Desktop refresh and validate the model
First complete a successful Gen2 refresh. The wizard does not load data during the upgrade. Rebind downstream dataflows by opening and saving them, then refresh them in dependency order. In Desktop, use Close & Apply and Home → Refresh on the affected authoring model. Opening a report or seeing old imported data is not proof that its source still works.
Compare row counts, key uniqueness, data types, nulls, date coverage, and control totals with your baseline. Inspect each query's Applied Steps, especially Changed Type, renamed/removed columns, merges, and expansions. Then test relationships, measures, slicers, drill-through, and RLS roles. Source schema changes can break transformations and model objects even when other tables still refresh.
6. Publish the repaired model and test Service refresh separately
After the Desktop checks pass, publish the validated model to your test workspace or update the intended existing model through your normal deployment process. Open its Service Settings → Gateway and cloud connections and Data source credentials, where applicable. Verify the new source mapping, connection access, and authentication. Run Refresh now and inspect refresh history; a successful local refresh does not validate the Service's credentials or gateway configuration.
Check every dependent report in the Service and test an appropriate consumer/RLS identity. Verify fresh dates and totals, not merely the absence of an error. For Import models, schedule or orchestrate the semantic-model refresh after the upstream Gen2/output refresh succeeds. Gen1 linked-dataflow cascading refresh is not carried over; replace that dependency with a pipeline or an appropriate schedule.
Why only some tables may need adjustment
In an Excelized migration, gateway access required attention and the dependent reports/semantic models needed review, while only some table queries required changes. That is a firsthand observation, not a universal Gen2 requirement. Mixed connector paths, table navigation, credentials, and schema differences are checks to investigate; without the original errors and query definitions, we cannot establish which caused each failure.
Use this acceptance rule: complete upstream refresh, successful refresh of every affected semantic model in Desktop where applicable and in the Service, reconciled business results, and working dependent reports. Record the checks per model and table so an unchanged table does not hide an untested one.
Final Thoughts
Gen2 can be a good fit when its destinations, Fabric orchestration, or monitoring solve a real requirement for your team. A stable Gen1 workflow can remain in place while you plan: Microsoft's migration guidance says existing Gen1 workloads are still supported, though Gen1 is in legacy status and won't receive new feature investment. Start with one table, preserve the old route with Save As, and agree on a rollback or recovery plan before a production cutover.
Your next action: write a one-page pilot checklist with the source snapshot, consumer inventory, expected totals, destination policy, access tests, and fallback owner. That turns a generation comparison into a decision your reporting team can verify.
Official References and Further Reading
Microsoft's migration pages and a recent Fabric Community update are linked beside the caveats they support. Product behavior can change during preview, so recheck the relevant pages before a production cutover.
Build on this workflow
Planning a dataflow change? Explore Excelized services for practical help with analytics workflows and reporting.
Need a second set of eyes on your migration plan?
Comments