Learn how to load data into Dataverse using Excel import, Power Query dataflows, and upsert logic with alternate keys. This deep-dive lesson covers realistic transformation scenarios, lookup resolution, sequencing for relational data, and troubleshooting the failures that actually happen in production.
You've spent weeks designing your Dataverse schema — relationships mapped, choice columns defined, security roles configured. And now someone from the business drops a 40,000-row Excel file on your desk and says "can we get this in by Monday?" If you've ever been in that moment, you know the gap between a clean data model and populated production data can feel surprisingly vast.
Data migration and ongoing data integration are two of the highest-stakes activities in any Dataverse project. Get it wrong and you end up with orphaned lookups, duplicate records, mismatched choice values, or — worst of all — silently corrupted data that looks fine until a user notices the wrong customer attached to a $2 million opportunity. Get it right and your users open the app on day one to find their world already there, organized, searchable, and ready to use.
By the end of this lesson, you'll be equipped to handle all of the major data loading patterns against Dataverse: one-time Excel imports for initial data loads, Power Query-based dataflows for repeatable ingestion pipelines, and upsert logic using alternate keys so you can run the same import twice without creating duplicates. We'll dig into the internals of how each mechanism works, where each one breaks down, and how to design a load strategy that survives contact with real-world messy data.
What you'll learn:
This lesson assumes you're comfortable with the Dataverse data model — tables, columns, rows, and relationships — at a working level. If you need a refresher on how tables and columns are structured, read Dataverse Fundamentals: Tables, Columns, and Rows Explained for Power Apps Makers before continuing.
You should also have a working understanding of how relationships and lookups connect tables, because the correct import sequence depends directly on that structure. The lesson on Designing a Dataverse Data Model: Relationships, Lookups, and Choice Columns is the right companion read here.
For the dataflows section, familiarity with Power Query's M language at a basic level — filtering, renaming, and merging queries — is assumed, though we'll walk through non-trivial examples.
Before touching a single button in the UI, it's worth stepping back and understanding what you're actually doing when you load data into Dataverse. Unlike pushing rows into a SQL table, Dataverse applies a full platform layer on top of every write operation. Every imported row goes through:
This matters because it means importing 50,000 rows is not the same as a SQL bulk insert. The platform is doing real work on every record. A naive import approach — ignoring this overhead — is why people end up with imports that time out, fail midway, or crawl at 200 records per minute when they expected 2,000.
Knowing this shapes your entire strategy. For large initial loads, you'll want to disable auditing temporarily, consider bypassing synchronous plugins if they're doing redundant calculation work, and import in sequences that respect relationship dependencies.
Warning
Disabling auditing during a bulk import is a legitimate performance optimization, but it means you lose the creation audit trail for those records. If your organization has compliance requirements around record provenance, document the import process and timestamps externally before disabling auditing.
The Excel import feature in Power Apps (accessed via Data > Import data > Import from Excel on any table) is the fastest path from a spreadsheet to Dataverse rows. It's designed for operational users doing one-time or occasional loads, and it works well within certain boundaries. It also breaks in very specific, predictable ways when you push against those boundaries — and knowing them in advance saves enormous frustration.
The Excel import process uses a template approach. You download a template from Dataverse that includes the correct column names in the exact format the importer expects, then populate it with your data.
Navigate to your target table via make.powerapps.com > Tables, select the table, and then choose Export > Export data or use the Import > Import from Excel button. When you use the import path and click "Download template," you get an XLSX file with column headers pre-set to the schema names (not the display names).
This template distinction matters. If you're working with a column you named "Contact Name" with a schema name of cre7f_contactname, the template column header will read cre7f_contactname. You must populate data using these schema-level names. If your source data uses display names, you'll need to rename columns before importing — either in Excel itself or upstream.
Column types and their expected formats:
2024-03-15 or 2024-03-15T09:30:00Z. Excel's default date formatting frequently produces values like 3/15/2024 which fail silently or get rejected.100000001, not Active.0 and 1, or true and false as strings.2500.00 works; $2,500.00 fails.Tip
To find the integer values for your Choice column options, navigate to the column in the table designer, expand the choices, and hover over each option or check the column properties. The integer values are assigned when choices are created and are visible in the column editor.
Lookup columns are the single biggest obstacle to a successful Excel import. Dataverse stores lookups as GUIDs internally, and the Excel importer expects those GUIDs in your source file. But your source data almost certainly has names, codes, or identifiers — not GUIDs.
You have two real strategies here:
Strategy 1: Pre-resolve lookups using exported data
Before your import, export the related table to get a mapping of business keys to GUIDs. For example, if you're importing Account records that reference a parent Business Unit, export the Business Unit table, get a mapping of business_unit_name → systemuserid (GUID), then use VLOOKUP or Power Query in Excel to replace the name column in your import file with the appropriate GUID.
This approach works but is manual and fragile — it breaks the moment a new related record is added between when you built the mapping and when you run the import.
Strategy 2: Use alternate keys for lookup resolution
This is the cleaner approach and connects directly to a feature we'll cover in depth later. If you've defined an alternate key on the related table using a business identifier (like an account number or employee ID), the import tools — particularly dataflows — can resolve lookups using that key rather than requiring the GUID directly.
For Excel import specifically, the lookup-by-alternate-key behavior is not supported in the basic Excel import tool. You'll need to either pre-resolve to GUIDs or switch to the dataflows method.
Once your file is prepared, use Import > Import from Excel, upload the file, and the importer will show you a column mapping screen. Here you can map source columns to destination columns if the names don't perfectly match.
After mapping, you'll see a row count and a Review Mapping option that shows any detected issues. Address warnings before submitting. The import runs asynchronously — you'll see it in the Import Jobs view (accessible via Data > Import data > Import history).
Import results classify rows as:
The error log you download from a partial failure is your most important diagnostic tool. It returns each failed row with an error message. Common error messages and their real causes:
"Object reference not set to an instance of an object" — almost always a GUID in a lookup column that doesn't match any existing record"The option value is not valid" — a Choice column contains a label string instead of the integer value"The length of the value exceeds the maximum allowed length" — a text column value exceeds the column's maximum length setting"A record with the matching key value exists" — a duplicate detection rule or alternate key constraint firedThe Excel import tool has a hard limit of 10,000 rows per file. For larger datasets, you either need to split the file or switch methods. Beyond row count, the Excel import is also synchronous from a user perspective — you're in the UI manually triggering it, which makes it unsuitable for anything requiring scheduling or repeat execution.
If any of these conditions apply, move to dataflows:
Dataverse dataflows are a Power Query-based ETL tool that runs in the Power Platform. They read from a source, apply M transformations, and write into Dataverse tables. Unlike the Excel importer, dataflows support scheduling, complex transformation logic, lookup resolution using alternate keys, and datasets of any size.
From an architecture standpoint, a dataflow is a Power Query project that executes in the cloud. The same M language you use in Power BI Desktop or Excel Power Query runs here — which is a significant advantage because it's a skill many data professionals already have.
Key insight
Dataflows are not a streaming or near-real-time tool. They are batch ETL. If you need real-time data integration, you want Power Automate flows or direct API calls. Dataflows shine for scheduled batch loads — nightly, hourly, or on-demand.
Navigate to make.powerapps.com, select your environment, and go to Dataverse > Azure Synapse Link — wait, wrong path. For dataflows, go to Data > Dataflows in the left navigation. Choose New dataflow, give it a name, and you're dropped into the Power Query editor.
The source selection supports a wide range of connectors: Excel files, SharePoint lists, SQL Server, Azure SQL Database, REST APIs via OData, Salesforce, and many others. The connector library is the same one used in Power BI, which means if you can build the query in Power BI Desktop, you can replicate it in a dataflow.
For our examples, we'll work with an Excel file stored in SharePoint — a common real-world scenario for teams migrating from spreadsheet-based processes.
Suppose you're migrating customer contact data from a legacy CRM export. The source Excel has these columns:
CustomerID | FullName | EmailAddress | PhoneNumber | Status | AccountCode | SalesRegion
Your Dataverse target has:
Contact table with standard fields plus a custom cr_customerid text columnStatus choice column with values: Active (100000000), Inactive (100000001), Prospect (100000002)Account table, where Account has an alternate key on accountnumberSalesRegion table with an alternate key on cr_regioncodeThis is a typical legacy migration — the source has readable labels and codes, not GUIDs, and you need to handle both choice value mapping and lookup resolution.
Here's a structured M query approach:
let
// Load source data from SharePoint
Source = Excel.Workbook(
SharePoint.Files("https://yourorg.sharepoint.com/sites/DataMigration",
[ApiVersion = 15]){[Name="CustomerExport.xlsx"]}[Content],
true, true
),
// Navigate to the correct sheet
CustomerSheet = Source{[Item="Customers",Kind="Sheet"]}[Data],
// Promote headers
PromotedHeaders = Table.PromoteHeaders(CustomerSheet, [PromoteAllScalars=true]),
// Trim all text columns to remove leading/trailing whitespace
TrimmedText = Table.TransformColumns(PromotedHeaders, {
{"FullName", Text.Trim},
{"EmailAddress", Text.Trim},
{"AccountCode", Text.Trim},
{"SalesRegion", Text.Trim}
}),
// Split FullName into FirstName and LastName
SplitName = Table.AddColumn(TrimmedText, "FirstName",
each Text.BeforeDelimiter([FullName], " "), type text),
AddLastName = Table.AddColumn(SplitName, "LastName",
each Text.AfterDelimiter([FullName], " ", {0, RelativePosition.FromEnd}), type text),
// Map Status labels to choice integer values
MapStatus = Table.TransformColumns(AddLastName, {
{"Status", each
if _ = "Active" then 100000000
else if _ = "Inactive" then 100000001
else if _ = "Prospect" then 100000002
else null, Int64.Type}
}),
// Normalize phone numbers to E.164 format
NormalizePhone = Table.TransformColumns(MapStatus, {
{"PhoneNumber", each
if _ = null then null
else "+" & Text.Remove(_, {" ", "-", "(", ")", "."}),
type text}
}),
// Select and rename to match Dataverse schema names
RenamedColumns = Table.RenameColumns(NormalizePhone, {
{"CustomerID", "cr_customerid"},
{"FirstName", "firstname"},
{"LastName", "lastname"},
{"EmailAddress", "emailaddress1"},
{"PhoneNumber", "telephone1"},
{"Status", "cr_status"},
{"AccountCode", "parentcustomerid"},
{"SalesRegion", "cr_salesregionid"}
}),
// Remove the FullName column — no longer needed
FinalColumns = Table.RemoveColumns(RenamedColumns, {"FullName"})
in
FinalColumns
After your query is clean in the Power Query editor, click Next to reach the Map tables screen. This is where you connect your query output to a Dataverse table and configure how each column maps.
For each query in your dataflow, you specify:
For lookup columns — parentcustomerid and cr_salesregionid in our example — the mapping interface lets you specify that the value in your source column is not a GUID but rather the value of an alternate key on the related table. This is the critical mechanism that makes dataflows far more practical than Excel import for any dataset involving lookups.
In the mapping screen, when you map your parentcustomerid source column to the Account lookup on Contact:
accountnumberNow when the dataflow runs, for each row it looks up the Account record whose accountnumber matches your AccountCode value and uses that record's GUID for the lookup. If no match is found, the row either fails or loads with a null lookup, depending on your error handling configuration.
Tip
Before configuring lookup resolution in a dataflow, verify that an alternate key actually exists on the related table and that it's in an Active status. Go to the related table in the maker portal, open Table properties, and check the Keys tab. If no alternate key exists, you'll need to create one before the dataflow will have anything to resolve against. See Dataverse Alternate Keys, Duplicate Detection, and Data Quality for the full setup process.
Once your dataflow is saved and activated, use the Schedule refresh option on the dataflow to configure automatic execution. Options range from hourly to monthly, with day-of-week and time-of-day granularity.
Scheduled dataflows run as the credentials of the user or service principal configured in the data source connection. For production use, always configure a service principal or a dedicated service account rather than a personal user account — personal accounts create a bus factor problem when the user leaves the organization or their password changes.
Dataflow run history is visible in the Dataflows list — click on any dataflow to see recent runs, their status (Success, Failed, In Progress), row counts processed, and timing. Failed runs show error details per-query.
Dataflows process data in micro-batches internally. For Dataverse destinations, the effective throughput varies significantly based on:
For large initial loads (500K+ rows), a dataflow may not be the fastest option even though it's the most manageable option. In those cases, consider using the Dataverse Web API directly from an Azure Data Factory pipeline or a custom script, which allows batch sizes and parallel thread control. The dataflow remains the right tool for ongoing scheduled loads up to a few hundred thousand rows per run.
Warning
If your Dataverse environment has synchronous plugins registered on the target table's Create/Update messages, those plugins fire for every row loaded by a dataflow. A plugin that makes external API calls or performs complex lookups can cause your dataflow to exceed API call limits or time limits per row. Test with a small batch first and monitor the plugin trace log.
The word "upsert" is a portmanteau of "update" and "insert" — it means: insert the record if it doesn't exist, update it if it does. Upserts are the key to making your data loads idempotent, which means running the same load twice produces the same result rather than creating duplicates.
This is not just a convenience feature. Idempotent loads are a reliability requirement for any production data integration. Networks fail, jobs restart, source systems re-export data that's already been loaded. Without upsert behavior, every retry creates duplicates and you end up managing a deduplication problem that compounds over time.
An alternate key in Dataverse is a column (or combination of columns) designated as a unique identifier alternative to the system-generated GUID primary key. When an alternate key is defined on a table, the Dataverse API supports a special form of PUT request that says: "find the record where this alternate key matches this value, and update it; if no such record exists, create it."
This is distinct from how the standard Dataverse API works. A normal Create call always creates. A normal Update call requires the GUID. The alternate key upsert pattern lets you use your own business identifier as the anchor.
For example, if your Contact table has an alternate key on cr_customerid (your legacy CRM's customer ID), then you can run an import that says: "if a Contact with cr_customerid = 'C-10042' exists, update it; otherwise create it." You can re-run this import weekly as your source system exports fresh data and you get a reliable sync without ever generating duplicates.
In the Power Apps maker portal, navigate to your table, open Table properties > Keys, and add a new key. Specify:
For a single-column key, choose the column that contains your unique business identifier — an employee ID, account number, SKU, or similar value. For composite keys (multiple columns together form a unique identifier), select all participating columns.
After saving, the key goes through an indexing process — the platform is creating a database index on those columns. This can take a few minutes for large tables. The key status moves from Pending to Active when indexing is complete. You cannot use the key in imports or API calls until it shows Active.
Note
Alternate keys impose a uniqueness constraint. If your table already has data with duplicate values in the column you're designating as an alternate key, the key creation will fail. You'll need to resolve duplicates first. This is actually a useful data quality forcing function — it surfaces problems you'd want to know about anyway.
In the dataflow's Map tables screen, after selecting your destination table, look for the Unique key or Row identifier setting. This is where you specify the alternate key that the dataflow should use to determine whether to create or update.
When you select an alternate key here:
This means you can run the same dataflow every night, and it will update changed records and create new ones without ever duplicating anything — as long as your source data's key column is clean and consistent.
When you need fine-grained control over upsert behavior — selective field updates, conditional logic, batch sizes — the Dataverse Web API is the right tool. This is the path for Azure Data Factory, Power Automate flows, or custom scripts.
The API endpoint for an upsert using an alternate key follows this pattern:
PATCH https://yourorg.crm.dynamics.com/api/data/v9.2/cr_contacts(cr_customerid='C-10042')
Content-Type: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
If-Match: *
{
"firstname": "Sarah",
"lastname": "Chen",
"emailaddress1": "sarah.chen@example.com",
"telephone1": "+14155550182",
"cr_status": 100000000,
"_parentcustomerid_value@odata.bind": "/accounts(accountnumber='ACC-00891')"
}
A few critical details in this request:
The URL format — cr_contacts(cr_customerid='C-10042') tells the API to locate the record using the alternate key column cr_customerid with value 'C-10042'. If the column is a text type, the value is quoted. If it's an integer, no quotes.
The If-Match: * header — without this header, the PATCH request will fail if no matching record exists (it behaves as a pure update). With If-Match: *, the API upserts — creating if not found, updating if found.
Lookup binding via @odata.bind — this is how you resolve lookups to related records by alternate key within the same API call. The pattern _parentcustomerid_value@odata.bind: "/accounts(accountnumber='ACC-00891')" tells the API to find the Account with accountnumber = 'ACC-00891' and use it as the lookup value. No GUIDs required.
Key insight
The @odata.bind pattern for lookup resolution in the Web API is the direct equivalent of the alternate key lookup configuration in dataflows. Both ultimately use the same platform mechanism — Dataverse resolves the alternate key to a GUID server-side. This is why defining alternate keys on related tables is a prerequisite for any serious data integration work.
For high-volume API-based upserts, sending one request per row is too slow. The Dataverse Web API supports OData batch requests, where you bundle up to 1,000 individual operations into a single HTTP request:
POST https://yourorg.crm.dynamics.com/api/data/v9.2/$batch
Content-Type: multipart/mixed;boundary=batch_abc123
OData-MaxVersion: 4.0
OData-Version: 4.0
--batch_abc123
Content-Type: multipart/mixed;boundary=changeset_def456
--changeset_def456
Content-Type: application/http
Content-Transfer-Encoding: binary
PATCH https://yourorg.crm.dynamics.com/api/data/v9.2/cr_contacts(cr_customerid='C-10042') HTTP/1.1
Content-Type: application/json
If-Match: *
{"firstname":"Sarah","lastname":"Chen","emailaddress1":"sarah.chen@example.com"}
--changeset_def456
Content-Type: application/http
Content-Transfer-Encoding: binary
PATCH https://yourorg.crm.dynamics.com/api/data/v9.2/cr_contacts(cr_customerid='C-10043') HTTP/1.1
Content-Type: application/json
If-Match: *
{"firstname":"Marcus","lastname":"Webb","emailaddress1":"marcus.webb@example.com"}
--changeset_def456--
--batch_abc123--
The batch endpoint dramatically improves throughput. Each changeset within a batch is atomic — either all operations in the changeset succeed or all roll back. This gives you transactional safety at the batch level.
Practical throughput with batch upserts: expect 2,000–5,000 records per minute depending on row complexity and plugin overhead. For a 500,000-row migration, that's 2–4 hours — plan accordingly and run initial loads during off-hours.
One of the most common migration failures happens when you try to import data without respecting the dependency order of your tables. If your Contact records reference Account records via a lookup, and the Account records don't exist yet in Dataverse, every Contact import will fail on the lookup resolution step.
The rule is simple: import parent tables before child tables. But in practice, your schema may have complex webs of relationships that make the right order non-obvious.
Start by drawing out your table dependency graph — literally, on paper or a whiteboard. Identify which tables have no incoming lookups (pure parents), which have lookups to other custom tables (middle tier), and which have lookups to multiple other tables (leaves).
A typical enterprise migration sequence might look like:
Warning
Be especially careful with self-referential lookups — a "Manager" lookup on a User table that points to another user in the same table, or a "Parent Account" lookup on Account that points to another Account. You can't import all records in one pass because the parent may not exist yet when you're importing the child. The solution is a two-pass import: first import all records with null for the self-referential lookup, then run a second pass that updates only the self-referential lookup column using upsert.
No migration survives contact with production source data without a data quality phase. The nature of that phase — and how much work it represents — is usually a function of how disciplined the source system was. CRM exports are often clean. Spreadsheet-based processes used by humans for ten years are rarely clean.
Build a data profiling step into your dataflow or pre-import process. In M, this looks like explicit type coercions with error handling:
// Safe conversion of date fields with error trapping
SafeDate = Table.TransformColumns(Source, {
{"LastContactDate", each
try Date.From(_)
otherwise null,
type nullable date}
})
Any value that can't be converted becomes null rather than crashing the row. You then add a calculated column that flags rows with unexpected nulls in required fields:
FlaggedRows = Table.AddColumn(SafeDate, "HasErrors", each
[CustomerID] = null or
[LastName] = null or
[AccountCode] = null,
type logical
)
Split your dataflow into two outputs: clean rows go to Dataverse, flagged rows go to an error log (a SharePoint list or another Dataverse table) for human review. This is a pattern borrowed from enterprise ETL — never silently discard bad data, always surface it for remediation.
For choice column mapping, build a reference table in your dataflow for the mapping rather than hardcoding it:
// Define the mapping table inline
StatusMapping = #table(
{"SourceLabel", "DataverseValue"},
{
{"Active", 100000000},
{"Inactive", 100000001},
{"Prospect", 100000002},
{"Lead", 100000002}, // Source system used "Lead" as a synonym for Prospect
{"Former", 100000001} // Map legacy "Former" to Inactive
}
),
// Join source data to mapping table
WithStatus = Table.NestedJoin(
CleanData, {"Status"},
StatusMapping, {"SourceLabel"},
"StatusLookup", JoinKind.LeftOuter
),
// Expand the joined column
ExpandedStatus = Table.ExpandTableColumn(
WithStatus, "StatusLookup",
{"DataverseValue"}, {"cr_status"}
)
This approach makes the mapping explicit and auditable — you can see exactly what source values map to what Dataverse values, and adding a new synonym is a one-line change rather than a buried conditional.
This exercise simulates a real migration scenario: loading product catalog data into a custom Dataverse table with lookup resolution and upsert behavior.
Scenario: Your organization is migrating a product catalog from a legacy system. You have an Excel export with 2,000 product rows. Products belong to Categories, which are stored in a separate Dataverse table. Some products already exist in Dataverse (imported in an earlier pilot) and should be updated, not duplicated.
Step 1: Set up your tables
Create a Category table with columns: cr_categoryname (Text, Required), cr_categorycode (Text, Required). Create an alternate key on cr_categorycode.
Create a Product table with columns: cr_productname (Text), cr_sku (Text, Required), cr_listprice (Currency), cr_isactive (Yes/No), and a lookup cr_categoryid to the Category table. Create an alternate key on cr_sku.
Step 2: Prepare source data
Create an Excel file with two sheets:
CategoryCode, CategoryNameSKU, ProductName, ListPrice, IsActive, CategoryCodePopulate with at least 20 categories and 100 products. Include 10 products whose SKUs you'll also add manually to Dataverse before the import (to test the update path of the upsert).
Step 3: Create the dataflow
Create a new dataflow. Create two queries: one for Categories, one for Products. In the Categories query, rename CategoryCode to cr_categorycode and CategoryName to cr_categoryname. In the Products query, apply the M transformations to rename all columns to schema names and convert IsActive from text ("Yes"/"No") to boolean (true/false).
Step 4: Configure mappings
Map the Categories query to the Category table, using cr_categorycode as the unique key. Map the Products query to the Product table, using cr_sku as the unique key. Configure the cr_categoryid lookup to resolve using the Category table's cr_categorycode alternate key.
Step 5: Run and validate
Run the Categories query first, then the Products query (in the dataflow settings, you can control load order). After the run completes, verify in Dataverse that:
Problem: Import completes but lookup fields are all null
This almost always means the alternate key used for lookup resolution doesn't exist, isn't Active, or the source values don't match the target values. Check: Does the alternate key exist on the related table? Is its status Active (not Pending)? Are there leading/trailing spaces in the source values that prevent matching? Run Text.Trim in your dataflow query on lookup key columns.
Problem: Choice columns appear as null after import
The source values are likely labels (strings) instead of integer values. Dataverse's Excel importer will silently set a Choice field to null when the value doesn't match a valid integer option. Add explicit mapping as shown in the M code above, and validate that integer values match what's configured in the column definition.
Problem: Dataflow runs successfully but records aren't appearing in the app
Check the security role of the user running the app. If records were created by a service principal or a different user, and the table uses owner-based access, users may not have read access to those records. This is a particularly common issue in environments using business unit security. The lesson on Dataverse Security: Business Units, Security Roles, and Teams covers this in depth.
Problem: DateTime columns import with the wrong date (off by hours)
Dataverse stores all DateTime values in UTC. If your source data is in a local timezone without an offset indicator, the importer assumes UTC. A column showing "9:00 AM" in New York's EST will store as 9:00 AM UTC, which displays as 4:00 AM EST in the app. Use timezone-aware source formats with explicit UTC offsets, or apply a UTC conversion in your M query using DateTimeZone.ToUtc(DateTimeZone.From([DateColumn], -5, 0)).
Problem: Dataflow fails with "API limit exceeded"
Dataflows call the Dataverse API for each row (or batch of rows). If your environment is also running other processes (Power Automate flows, other dataflows, user activity), you may hit the service protection API limits. Run large dataflows during off-peak hours, and if this is a recurring issue on initial loads, consider the Web API batch approach with throttling built into your calling code.
Problem: Import creates records but business rules are rejecting some
Synchronous business rules run during import just as they run during normal user entry. A business rule that makes a field required based on another field's value will cause records to fail if the import data doesn't satisfy the condition. You have two options: fix the source data to satisfy the rule, or temporarily set the business rule scope to a specific form (instead of "All Forms" which includes API access) during the import. Remember to revert scope after the import. See Business Rules in Dataverse: Validation and Field Logic Without Code for scope configuration.
Problem: Upsert is creating duplicates instead of updating
The alternate key lookup is case-sensitive for text columns by default. If your source has ACC-00891 and your Dataverse data has acc-00891, these won't match and the upsert will create a new record. Normalize case in your M query using Text.Upper or Text.Lower on the key column, and make sure the alternate key column in Dataverse has consistent casing in its existing data.
Once you've mastered full-table loads, the natural next step is incremental loading — only loading rows that have changed since the last run. This dramatically reduces load time and API consumption for large tables with frequent but partial changes.
The watermark pattern works like this: your source system has a ModifiedDate or LastUpdated column. You store the timestamp of the last successful dataflow run (in a Dataverse table or a SharePoint list). Each dataflow run reads only source rows where ModifiedDate > LastSuccessfulRun, processes those, and then updates the stored watermark.
In M:
// Read the last watermark from a Dataverse table
WatermarkSource = Dataverse.Feed("https://yourorg.crm.dynamics.com/api/data/v9.2/"),
WatermarkTable = WatermarkSource{[Name="cr_dataflowtimestamps"]}[Data],
LastRun = WatermarkTable{[cr_flowname="ProductCatalogSync"]}[cr_lastsuccessfulrun],
// Filter source to only changed rows
ChangedRows = Table.SelectRows(SourceData,
each [ModifiedDate] > LastRun
)
After a successful run, a second query in the same dataflow updates the watermark record using upsert. This keeps your loads fast and targeted as your dataset grows.
Data loading and migration sit at the intersection of data engineering and platform configuration — and getting them right requires understanding both the mechanics of the tool and the architecture of the data. Here's what we covered:
Excel Import is the right tool for one-time loads of under 10,000 rows where the source data is relatively clean, lookups are pre-resolved to GUIDs or the dataset has no lookups, and you don't need scheduling. It's a good operational tool for business users doing manual imports.
Dataflows are the right tool for anything that needs to repeat, anything that requires transformation logic, and any scenario where you need lookup resolution by alternate key. The Power Query M language gives you serious data wrangling power, and the mapping screen handles the Dataverse-specific concerns of lookup binding and upsert configuration.
Upserts with Alternate Keys are not optional for production integration work — they're the mechanism that makes your loads safe to re-run. Define alternate keys on every table that will receive data from external systems, and use them consistently in both your dataflows and any direct API calls.
The logical progression from here connects deeply with the rest of your Dataverse model work. If you haven't yet configured the forms and views your users will use to see this imported data, the lessons on Designing Model-Driven Forms: Sections, Tabs, Subgrids, and Quick View Forms and Creating and Customizing Views in Model-Driven Apps: Filters, Sorting, and Editable Grids cover how to surface data effectively once it's in Dataverse.
If your data integration involves column-level sensitivity — certain columns should only be readable by specific roles — read Column-Level Security and Record Sharing in Dataverse to understand how to protect sensitive fields that your import pipeline is populating.
A well-executed data migration is often the moment a project goes from theoretical to real for your stakeholders. When they open the app and see their actual data — clean, organized, and linked correctly — the platform stops being abstract and starts being their tool. That moment is worth getting right.