Configuring Dataverse Dataflows and Power Query Transformations: Building Repeatable Data Ingestion Pipelines into Model-Driven App Tables
Learn how to build production-quality Dataverse dataflows with Power Query transformations that reliably sync external data into model-driven app tables. Covers upserts, alternate key mapping, incremental refresh, multi-table dependencies, and operational monitoring — everything you need to run a pipeline a team can trust.
Configuring Dataverse Dataflows and Power Query Transformations: Building Repeatable Data Ingestion Pipelines into Model-Driven App Tables
Introduction
You've built the data model. The tables are structured, relationships are configured, security roles are in place, and your model-driven app is ready to serve real users. Then someone asks the question that every maker eventually hears: "How do we actually get our data in here?"
For one-time migrations, a manual Excel import might be enough. But the scenario you'll encounter far more often is the ongoing ingestion problem — a sales team pushing opportunity data from a legacy CRM every morning, a finance team syncing invoice statuses from an ERP nightly, an operations team consuming flat files dropped by a third-party logistics provider every few hours. These are repeatable ingestion requirements, and they demand a repeatable, maintainable solution. That solution is Dataverse Dataflows.
Dataflows sit in an interesting architectural position. They're not ETL tools in the full Databricks sense, nor are they lightweight Power Automate flows that handle one record at a time. They're a middle layer: Power Query transformations wrapped in a managed, scheduled engine that writes directly into Dataverse tables. By the end of this lesson, you'll know how to design, build, and operate a production-quality dataflow pipeline — including the transformation logic, the upsert mapping, the refresh scheduling, and the failure-handling discipline that separates a brittle one-off from something a team can rely on for years.
What you'll learn:
How Dataverse Dataflows are architecturally positioned relative to other ingestion options and when to choose them
How to configure a dataflow from scratch, connect to source data, and map it to Dataverse tables
How to write Power Query M transformations that handle real-world data quality problems
How alternate keys, upserts, and merge behaviors control how records are created versus updated
How to schedule, monitor, and troubleshoot dataflow runs in production
Advanced patterns for multi-table ingestion, incremental refresh, and dependency management between dataflows
Prerequisites
You should be comfortable with the following before working through this lesson:
Familiarity with the Power Query M language at a basic level (transforming columns, filtering rows, changing types)
Access to a Power Apps environment with the Dataflows feature enabled (requires a Dataverse-backed environment, not just a default trial)
Why Dataflows, and When Not to Use Them
Before you open the dataflow editor, it's worth being honest about where dataflows fit in the broader ingestion landscape — because they're genuinely the right tool in some situations and the wrong tool in others.
Dataflows use a batch processing model. You define a set of Power Query queries, the engine runs them on a schedule or on demand, and the results are loaded into Dataverse tables. The key characteristics that follow from this model are:
Latency is measured in minutes to hours, not seconds. If a source record changes in your ERP and you need that change reflected in Dataverse within 30 seconds, a dataflow won't serve you. You need a Power Automate flow with an event trigger, or a direct API write. Dataflows are the right answer when near-real-time isn't required — financial reporting data, daily snapshots, nightly sync jobs, weekly file drops.
They're designed for structured, queryable sources. Dataflows connect to databases, files (CSV, Excel, Parquet), SharePoint lists, OData feeds, REST APIs (via the Web connector), Azure Blob/Data Lake, and many SaaS systems. If your source is unstructured, event-driven, or requires complex authentication flows per record, Power Automate or Azure Data Factory is a better fit.
They handle volume well, but not unlimited volume. Microsoft has evolved the capacity ceiling over time, but dataflows are most reliable in the tens-of-thousands to low-millions of rows per refresh. Extremely large tables (tens of millions of rows per refresh cycle) will hit throttling and timeout behaviors that require careful engineering.
They produce auditable, reproducible transformations. Because the transformation logic lives in Power Query M, it's readable, versionable (if you export your solution), and not buried in someone's Excel formula. This is a significant advantage over ad-hoc import workflows.
The most common mistake when introducing dataflows is using them for everything. Use Power Automate for event-driven, record-level operations. Use dataflows for scheduled, set-based operations. Mixing these concerns into the wrong tool produces systems that are either too slow or unnecessarily fragile.
Anatomy of a Dataverse Dataflow
A dataflow is composed of several distinct layers, and understanding each layer is essential before you start configuring anything.
Queries (the transformation layer)
A dataflow contains one or more Power Query queries. Each query is a named, potentially reusable M expression that sources data, applies transformations, and produces a tabular output. Queries can reference each other — you can write a base query that pulls raw data from a source, then write multiple downstream queries that filter or reshape that base. This is the foundation of modular, maintainable transformation logic.
Entity mappings (the load layer)
Each query that you designate for loading gets mapped to a Dataverse table (called an "entity" in the dataflow UI). The mapping specifies:
Which Dataverse table receives the data
How each column in the query output maps to a column in the table
The load behavior: create-only, upsert (match on alternate key), or delete-and-reload
The refresh engine
When a dataflow runs — either manually or on schedule — the engine evaluates each query in dependency order, applies transformations, and executes the write operations against Dataverse. The engine is cloud-hosted; your local machine plays no role during scheduled refreshes.
Linked entities (optional)
A powerful feature for multi-dataflow architectures: a dataflow can reference a query output from another dataflow rather than pulling from an external source directly. This enables fan-out patterns where one "staging" dataflow prepares reference data and multiple downstream dataflows consume it.
Setting Up Your First Dataflow
Navigate to make.powerapps.com, select your environment, and in the left navigation, expand Dataverse and choose Dataflows. If you're in an environment that hasn't had dataflows created before, you'll see an empty state with a New dataflow button.
Click New dataflow and give it a meaningful name — something that describes the source and target, like SalesOrderSync_ERPtoDataverse or ProductCatalog_SharePointIngestion. Naming conventions matter at scale; a name like "Dataflow1" becomes a liability when you have twelve of them.
Choosing your data source
The connector gallery appears immediately. The most commonly used sources for Dataverse ingestion are:
SQL Server / Azure SQL Database — the most reliable for high-volume, well-structured source data
SharePoint Online list — useful for semi-structured reference data managed by business users
Excel / CSV from SharePoint or Azure Blob — for file-based source patterns from third-party vendors
OData — for API-based sources that expose a standard feed, including other Dataverse environments
Web (HTTP) — for REST APIs that don't have a dedicated connector
Azure Data Lake Storage Gen2 — for enterprise data lake patterns with Parquet or CSV files
For this lesson, we'll use an Azure SQL Database scenario: a source system that contains a SalesOrders table with order header data, and we want to sync it into a Dataverse custom table called contoso_salesorder.
After selecting the SQL Server connector, you'll provide the server address, database name, and authentication credentials. For production dataflows, prefer Service Principal or Database credentials over Windows authentication, since the dataflow runs in the cloud and Windows Kerberos flows are unreliable across network boundaries.
Warning
Never store service account credentials directly if your environment supports OAuth or Azure AD authentication. If you're connecting to Azure SQL, configure the database to accept Azure AD authentication and use a service principal. This avoids credential rotation pain and audit gaps.
Once connected, the navigator shows the tables and views available in the database. Select the SalesOrders table and click Transform data. This opens the Power Query editor — the heart of the dataflow configuration experience.
Writing Real-World Power Query Transformations
The Power Query editor in Dataflows is functionally identical to the editor in Power BI Desktop and Excel's Get & Transform. Every action you take in the visual editor generates M code in the formula bar. Learning to read and write that M code directly is what separates an expert dataflow developer from someone who can only click through the UI.
Understanding M query structure
An M query is a let...in expression. Each step in the query is a named binding inside the let block, and the in clause specifies which step's output is returned. Here's the structure of a typical production query:
let
Source = Sql.Database("contoso-erp.database.windows.net", "SalesDB"),
SalesOrders_Raw = Source{[Schema="dbo", Item="SalesOrders"]}[Data],
FilteredActive = Table.SelectRows(SalesOrders_Raw, each [Status] <> "DELETED"),
RenamedColumns = Table.RenameColumns(FilteredActive, {
{"OrderID", "contoso_externalorderid"},
{"CustomerNumber", "contoso_customernumber"},
{"OrderDate", "contoso_orderdate"},
{"TotalAmount", "contoso_totalamount"},
{"Status", "contoso_orderstatus"}
}),
TypedColumns = Table.TransformColumnTypes(RenamedColumns, {
{"contoso_externalorderid", type text},
{"contoso_customernumber", type text},
{"contoso_orderdate", type date},
{"contoso_totalamount", type number},
{"contoso_orderstatus", type text}
}),
TrimmedText = Table.TransformColumns(TypedColumns, {
{"contoso_externalorderid", Text.Trim},
{"contoso_customernumber", Text.Trim},
{"contoso_orderstatus", Text.Trim}
})
in
TrimmedText
A few things worth noting in this example. First, the column renaming uses the target Dataverse column schema names directly — this simplifies the mapping step later and makes the intent of each column explicit in the query itself. Second, explicit type conversion happens before trimming, because M's Text.Trim function requires text columns. Third, deleted records are filtered out at the query level rather than being loaded into Dataverse and then cleaned up — this is almost always the right pattern.
Tip
Always rename columns to match your Dataverse target schema names inside the Power Query transformation, not via the mapping UI. It makes the mapping step trivial (just auto-map) and makes your query self-documenting. When someone reads the query six months later, they know exactly where each column is going.
Handling data quality issues
Real source data is never clean. Here are the patterns you'll need most often:
For text fields where null should become an empty string rather than cause a load error:
NullsToEmpty = Table.TransformColumns(ReplacedNulls, {
{"contoso_orderstatus", each if _ = null then "" else _}
})
Date parsing from inconsistent string formats:
Source systems frequently store dates as VARCHAR with inconsistent formats. Rather than failing the entire refresh on a single bad date, use error-safe parsing:
This approach catches parse failures per cell and replaces them with null rather than crashing the query. For production systems, you might prefer to log these failures separately — which requires a more sophisticated pattern involving Table.AddColumn with error capture and a separate "rejected rows" query.
Deduplication before load:
If your source can produce duplicate rows for the same logical record (common in denormalized views or file exports), deduplicate before loading:
This keeps only the first occurrence of each order ID. If you need the latest version rather than the first, sort descending on a modification timestamp before deduplicating:
This is where things get interesting — and where dataflow design diverges sharply from simple file imports.
Your contoso_salesorder table has a lookup column to a contoso_account table (the customer). The source data has a CustomerNumber field, not a Dataverse GUID. To populate the lookup correctly, you need to resolve the customer number to a Dataverse record ID.
The pattern is: load the Dataverse table containing accounts as a reference query, then merge it with your orders query on the customer number.
Create a second query in your dataflow to pull from the Dataverse connector:
let
Source = CommonDataService.Database("https://yourorg.crm.dynamics.com"),
Accounts_Table = Source{[Entity="account"]}[Data],
SelectedColumns = Table.SelectColumns(Accounts_Table,
{"accountid", "contoso_customernumber"}
)
in
SelectedColumns
Then in your orders query, merge after the deduplication step:
When you use the Dataverse connector inside a dataflow to read back existing records for lookup resolution, you're creating a circular dependency if the target is the same environment. This works, but it means your dataflow's accuracy depends on the state of Dataverse at refresh time. If the referenced account records haven't been loaded yet (e.g., in a first-time full load), the left join will produce nulls for the lookup column, and those records will load without the relationship populated. Always load reference tables before dependent tables in a multi-table dataflow — or use a two-pass approach where the first run loads parent records and a second scheduled run loads children.
Configuring the Entity Mapping
Once your query produces the right shape of data, you need to map it to a Dataverse table. After completing the query editor and clicking Next, you reach the Map tables step.
Selecting the target table
Choose Load to existing table and select your target Dataverse table from the dropdown. The UI will attempt to auto-map columns based on name matching. If you followed the advice above and renamed your columns to match Dataverse schema names, most columns will map correctly on the first pass.
Review the mapping carefully:
Columns shown in green are successfully mapped
Columns shown in yellow have type mismatches (fix these — don't ignore them)
Columns shown as unmapped will not be loaded
For lookup columns, the mapping UI expects you to map to the lookup column's schema name (e.g., contoso_accountid). The value in that column should be the Dataverse GUID of the target record — which is exactly what the join pattern above produced.
Configuring upsert behavior with alternate keys
This is the most important configuration decision in a dataflow for ongoing sync scenarios. You have two options:
Delete and load — truncates the table and reloads everything. Only appropriate for small reference tables where you own all the data and no other process writes to the same table. Never use this for tables with related records unless you've thought through cascade delete implications.
Upsert (match on alternate key) — compares each incoming row against existing Dataverse records using an alternate key column. If a match is found, the existing record is updated. If no match is found, a new record is created.
The alternate key must be configured on the Dataverse table before the dataflow can reference it. In our example, contoso_externalorderid (the source system's order ID) should be defined as an alternate key on the contoso_salesorder table. This is covered in detail in Dataverse Alternate Keys, Duplicate Detection, and Data Quality.
In the dataflow mapping UI, select Upsert as the update method, then choose your alternate key from the dropdown. The engine will now use the alternate key to decide create-versus-update for each row.
Key insight
Alternate keys don't just enable upserts — they also define uniqueness guarantees in Dataverse that prevent duplicate records regardless of whether a dataflow or another process is writing the data. This double-duty is a compelling reason to always define alternate keys on tables that receive data from external systems, even if you're not using dataflows yet. The Dataverse Alternate Keys, Duplicate Detection, and Data Quality article walks through the index implications in detail.
Handling choice columns
If your Dataverse table has a Choice column (Option Set) that you're trying to populate from the source, the dataflow engine expects the integer value of the choice option, not the label string. This trips up most people the first time.
Your source data probably has status values like "Open", "Closed", "Pending". Your Dataverse choice column has those same labels with underlying integer values (e.g., 100000000, 100000001, 100000002). You need to transform the string labels to integers in your Power Query:
MappedStatus = Table.TransformColumns(ExpandedAccountId, {
{"contoso_orderstatus", each
if _ = "Open" then 100000000
else if _ = "Closed" then 100000001
else if _ = "Pending" then 100000002
else null
, Int64.Type}
})
Document these mappings in your query as comments or in an external data dictionary. Choice integer values are environment-specific and can differ between development and production if not managed carefully.
Scheduling, Monitoring, and Operational Discipline
A dataflow that runs once manually is a curiosity. A dataflow that runs reliably on a schedule with observable behavior and clear failure handling is an operational asset.
Configuring the refresh schedule
After saving your dataflow, navigate to it in the Dataflows list and open its settings. The Refresh settings section lets you configure:
Automatic refresh toggle — must be explicitly enabled
Refresh frequency — daily, weekly, or hourly intervals
Time zone and start time — critical to get right relative to your source system's maintenance windows and your business's reporting cadence
A common pattern for nightly sync jobs is to schedule the dataflow to run at 2:00 AM in the time zone where the source system is located, with a 30-minute buffer after the source system's end-of-day batch completes. If your source system's batch finishes at 1:00 AM and you schedule your dataflow at 1:05 AM, you'll often catch partial data.
For hourly dataflows, verify your source system can handle the query frequency without degradation. Most relational databases handle this easily, but some API-based sources have rate limits that an hourly pull will breach.
Tip
If you need sub-hourly refresh, dataflows are likely not the right tool. The minimum schedule interval is one hour. For more frequent ingestion, consider Power Automate flows triggered by source system events, or Azure Data Factory pipelines with shorter triggers.
Monitoring refresh history
Each dataflow maintains a Refresh history log accessible from the dataflow management screen. Each refresh shows:
Status (Success, Warning, Failed)
Start time and duration
Row counts: rows loaded, rows with errors, rows skipped
The Warning status is worth explaining — a refresh can complete with warnings when some rows fail to load but the overall operation doesn't fail. This happens when individual records have validation errors (required field missing, type mismatch, alternate key conflict). The rows that failed are skipped, and the successful rows are loaded. This is actually the default behavior and is often acceptable, but you need to actively check that your warning row counts aren't growing over time.
Understanding and handling load errors
When rows fail to load, the dataflow UI shows error details per entity. Click into the entity's error log to see messages like:
Duplicate key violation on alternate key 'contoso_externalorderid' — this means the same alternate key value appeared more than once in your query output, which should have been caught by deduplication in Power Query
Required field 'contoso_name' has no value — your transformation is producing nulls for a required column; trace this back to source nulls and add null handling
Invalid lookup value — the GUID you're writing to a lookup column doesn't correspond to an existing record in the target table
The most dangerous load errors are the silent ones — the warning status that shows "250 rows failed" with no immediate alarm. Build a Power Automate flow that monitors dataflow refresh completion and sends a notification when the failure row count exceeds a threshold. This is a one-hour investment that prevents production data quality degradation going undetected for weeks.
Advanced Patterns
Incremental refresh
By default, every dataflow refresh reads the entire source dataset, transforms it, and upserts all records. For large tables, this is wasteful and slow. Incremental refresh solves this by querying only records that changed since the last refresh.
The technique requires your source table to have a reliable ModifiedDate or similar watermark column. In Power Query, you parameterize the date filter:
Create a Power Query parameter named LastRefreshDate with a default value of a distant past date (e.g., #date(2000, 1, 1)). Then filter your source query:
FilteredByDate = Table.SelectRows(SalesOrders_Raw,
each [ModifiedDate] >= LastRefreshDate
)
For true incremental refresh, you need to update LastRefreshDate after each successful run. Dataflows in Power Apps don't natively persist parameters between runs the way Power BI Premium's incremental refresh does. The workaround is to store the last successful refresh timestamp in a Dataverse table (a simple "Dataflow Run Log" table with one column) and read it at the start of your dataflow query:
let
Source = CommonDataService.Database("https://yourorg.crm.dynamics.com"),
RunLog = Source{[Entity="contoso_dataflowrunlog"]}[Data],
LastSuccessful = List.Max(RunLog[contoso_refreshdate]),
Watermark = if LastSuccessful = null then #date(2000,1,1) else LastSuccessful
in
Watermark
Then reference Watermark in your orders query instead of a hardcoded parameter. After a successful run, a separate Power Automate flow writes a new record to the run log table with the current timestamp. This is a hand-rolled incremental refresh, and it works reliably.
Note
Microsoft has been evolving the incremental refresh capabilities for Dataverse dataflows. As of this writing, the native incremental refresh controls available in Power BI dataflows are not fully exposed in the Power Apps dataflows UI. Always verify the current state of this feature in your environment before designing around it, and treat the manual pattern above as a stable fallback.
Multi-table dataflows with dependency ordering
When ingesting data into a table structure with parent-child relationships — say, loading Accounts before Contacts, or loading Products before Order Lines — the execution order within a single dataflow matters.
The dataflow engine respects query dependencies when they're modeled explicitly (i.e., one query references another). But for load ordering across tables in the same dataflow, you're relying on the engine's internal sequencing, which isn't fully deterministic from the outside.
A more reliable pattern is to use separate dataflows with explicit scheduling offsets:
ReferenceData_Dataflow runs at 2:00 AM — loads Accounts, Products, Territories
TransactionalData_Dataflow runs at 3:00 AM — loads Orders, Order Lines, Contacts
The 60-minute offset ensures the reference data is fully loaded before the transactional dataflow starts resolving lookups. This is more operationally visible than trying to manage intra-dataflow ordering.
For tighter coupling, you can trigger the second dataflow from Power Automate using the Power Platform connector's Run Dataflow action, fired after the first dataflow's refresh completes. This eliminates the arbitrary time offset and makes the dependency explicit.
Transforming hierarchical source data into flat Dataverse tables
Source systems sometimes expose data in nested structures — JSON from a REST API, XML from a legacy SOAP feed, or multi-level group-bys in a CSV. Power Query handles this, but the technique is worth showing explicitly.
For a JSON source that returns orders with embedded line items:
This flattens the nested structure into one row per line item. The OrderID column acts as the foreign key that you'll use to resolve the lookup to the parent order record when loading line items.
Handling schema changes in source data
One of the most common failure modes for long-running dataflows is a schema change in the source — a column gets renamed, a new required column is added, a data type changes. Power Query queries fail hard when expected columns aren't present, which causes the entire refresh to fail.
Defensive M code uses try...otherwise and explicit column selection to absorb these changes:
This selects only the columns that exist in both the source table and your expected list. Missing columns won't cause a failure — but they also won't load, so you need a downstream alert mechanism. Balance defensive code with observability.
Security Considerations
Data ingestion pipelines are a privileged operation. The service account or service principal that runs the dataflow needs Dataverse permissions to create and update records in the target tables. Here's the security model you should build:
Dataflow credentials are stored as a Connection in the Power Apps environment. The user or service principal whose credentials are used becomes the effective caller for all Dataverse writes. Those writes will be stamped with that identity as the creator/modifier of records.
For model-driven apps where record ownership matters for security role filtering, consider whether you want dataflow-created records owned by a service account user or by an actual business user. If records must be owned by specific users (e.g., assigning records to the account manager responsible for them), your transformation needs to map ownerid to the appropriate user GUID, or you use a Power Automate post-processing step to reassign ownership after ingestion.
Dataflow connections are environment-specific but not solution-aware in the traditional sense. When you export a solution containing a dataflow and import it into a production environment, the connection references must be manually reconfigured in the target environment. Automate this in your deployment playbook — it's a common cause of post-deployment failure that looks mysterious until you check the connection status.
Hands-On Exercise
This exercise builds a complete dataflow that syncs product data from a SharePoint list into a Dataverse custom table. Work through it in a development environment.
Setup
Create a Dataverse custom table called contoso_product with the following columns:
contoso_productcode — Text, required, configured as an alternate key
Create a SharePoint Online list called ProductCatalog with columns matching the source schema: ProductCode, ProductName, UnitPrice, Category (text), IsActive (Yes/No).
Add at least 20 rows of realistic product data. Include 2-3 rows with the same ProductCode to test deduplication.
Build the dataflow
Navigate to Dataverse > Dataflows in make.powerapps.com and create a new dataflow named ProductCatalog_SharePoint_to_Dataverse.
Connect to your SharePoint Online list using your tenant credentials.
In the Power Query editor, write transformations to:
Remove duplicate rows based on ProductCode (keep the first occurrence)
Rename all columns to match Dataverse schema names
Transform the Category text column to integer choice values
Handle any null values in UnitPrice by replacing with 0
Explicitly type all columns
Map the query output to contoso_product using Upsert on the contoso_productcode alternate key.
Save and run the dataflow manually. Verify row counts in the refresh history.
Modify three rows in your SharePoint list (change UnitPrice values) and run the dataflow again. Verify that the existing records were updated, not duplicated.
Enable automatic refresh on a daily schedule at a time of your choosing.
Challenge extension
Add a second query to your dataflow that produces a "staging summary" — a count of rows by category. Try to load this summary into a separate contoso_productstagingsummary table. Consider: what should the alternate key be on the summary table? What happens if you change the category distribution in your source list?
Common Mistakes & Troubleshooting
"My dataflow succeeds but no records are loaded"
This almost always means the alternate key column in your query output has a data type mismatch with the Dataverse column definition. If contoso_productcode is a Text column in Dataverse but your query produces a Number type, the mapping silently fails to match rows. Fix: add explicit type conversion in your Power Query (Text.From([ProductCode])) and verify the mapping UI shows green for all columns.
"The dataflow runs, but lookups are always null"
The join step that resolves GUIDs is producing no matches. Check:
Is the join key (e.g., CustomerNumber) being trimmed/lowercased the same way in both the source query and the Dataverse reference query?
Did the reference records actually load in the same run? Remember the dependency ordering issue — if you're loading parent and child records in the same dataflow, the parent lookup table might not exist yet when the child query executes.
Is the Dataverse connector in your reference query pointing to the same environment as your target? It's easy to accidentally have a development environment dataflow reading from a production reference table.
"My dataflow fails with 'Invalid token' or 'Connection expired'"
OAuth tokens used by the dataflow connections have expiration periods. If a connection hasn't been used recently or if the service account password changed, the token expires. Navigate to Connections in the Power Apps maker portal, find the affected connection, and reauthenticate it. For production environments, prefer service principals with long-lived credentials over delegated user credentials to avoid this.
"Rows are being duplicated instead of upserted"
The alternate key isn't being matched. Possible causes:
The alternate key isn't active yet — Dataverse alternate key indexes take time to build (up to several minutes for large tables); running a dataflow against a newly created alternate key before the index is ready can bypass upsert behavior
The alternate key column value in your query has trailing whitespace or case differences relative to existing records — use Text.Trim and Text.Lower on both sides of the key
You selected the wrong update method in the mapping UI — confirm it's set to Upsert, not Create
"The dataflow is very slow on large tables"
Default behavior is a full table scan on every refresh. If you haven't implemented incremental refresh, you're pulling and processing the entire source dataset every time. For tables over 100,000 rows, implement the watermark-based incremental pattern described earlier. Also check whether query folding is happening — if your Power Query transformations can be translated back into a SQL WHERE clause and executed server-side, the query runs much faster than pulling all rows into the cloud transformation engine.
To verify query folding, right-click any step in the Power Query editor and check whether View Native Query is available and shows a SQL statement. If it's grayed out, that step (and all downstream steps) are running in the cloud M engine rather than in the source database.
Tip
Keep your filter and row-reduction steps as early as possible in your query — ideally immediately after the Source step. This maximizes the chance that query folding pushes filtering to the source database rather than pulling the full dataset into the transformation engine.
"My dataflow configuration is lost after solution import"
Dataflows are solution-aware as of recent platform updates, but the connection references they depend on are not automatically reconfigured in the target environment. When importing a solution containing a dataflow, you must:
Ensure the same connectors (SQL, SharePoint, etc.) have active connections in the target environment before import
After import, open the dataflow and verify all connections are pointing to the correct target-environment data sources
Confirm the entity mappings still reference the correct tables (they typically do if the solution component is correct, but verify)
Summary & Next Steps
You've now covered the full lifecycle of a production Dataverse dataflow — from architectural positioning through query authoring, mapping configuration, upsert mechanics, scheduling, monitoring, and the edge cases that bring systems down in production. The key ideas to carry forward:
Transformations belong in Power Query. The M expression inside your dataflow is the authoritative definition of how source data maps to your Dataverse schema. Keep it readable, keep it modular (reference queries for shared logic), and keep it defensive against source data variability.
Alternate keys are the linchpin of repeatable ingestion. Without them, every dataflow refresh creates duplicates. With them, you get idempotent upserts that make your pipeline safe to re-run after failures.
Dependency ordering prevents ghost lookups. Load reference data before transactional data. Use scheduled offsets or Power Automate triggers between dataflows rather than trusting intra-dataflow ordering.
Observability is not optional. A dataflow that silently loads 80% of rows is worse than one that fails loudly, because silent degradation is invisible. Build monitoring — even a simple Power Automate notification on warning status — before you hand the pipeline to a production team.
The move from "I can import data" to "I operate reliable, monitored data pipelines" is what distinguishes a Power Platform architect from a Power Platform user. Dataflows, done well, are a cornerstone of that capability.
