Forum Discussion
Desktop connected to Dataflow Gen2 Key didn't match row Error
Hi jessimica1018 , Thank you for reaching out to the Microsoft Community Forum.
Since disconnecting/reconnecting and moving transformations to the Dataflow didn’t fully work, let’s try below:
Verify the `Assignment` table by running this SQL query in SQL Server: SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Assignment'. Then, in Dataflow Gen2, right-click the "Navigation" step, select "Refresh Preview," or use "Refresh Schema" to update metadata. To fix the intermittent error due to concurrency, schedule your Dataflow refresh during low-activity periods. If the table changes often, create a staging table in SQL Server with stable data and query that instead.
For the slowness, push the `Text.ToLower` transformation to SQL Server with this view: CREATE VIEW vw_Assignment AS SELECT LOWER(AM_Email) AS AM_Email, LOWER(Rec_Email) AS Rec_Email, LOWER(CM_Email) AS CM_Email, * FROM Assignment. Connect your Dataflow to `vw_Assignment`.Further optimize by applying early filters to reduce data volume, enabling "Enhanced Compute Engine" in Dataflow settings, adding indexes to the `Assignment` table’s email columns, and ensuring your On-Premises Data Gateway runs on a high-performance machine. Test the refresh to confirm the error is gone and load times are better.
If none of the above work, the best next step is to report the issue to Microsoft Support, as it may be a bug or a backend issue. Provide them with all the troubleshooting steps you've already taken to help them understand the issue better and provide a solution.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
That makes complete sense! So if I can't set this up in sql just because of the merging of a few tables and the steps/transformation I have the dataflow doing, I also need the data refreshing as frequently as possible. What is the best way to make sure when it's refreshing I don't lose info and cause the key error? Connecting desktop to a lakehouse or warehouse would remove my ability to further filter that dataflow, correct? Same with creating a semantic model (don't think that would solve it, it'd be the same issue).
Hi jessimica1018 , Thank you for reaching out to the Microsoft Community Forum.
First, modify your Dataflow to write its output to a Fabric Lakehouse table after merging tables and applying transformations like Text.ToLower. In Dataflow Gen2, set the destination to a Lakehouse, creating a stable table. This ensures Power BI queries a consistent dataset, eliminating key errors caused by SQL Server changes during refreshes. Next, enable incremental refresh in Dataflow Gen2 to support frequent updates without data loss. Use a date column in your Assignment table to track changes and set the policy to refresh only new or updated rows. Schedule refreshes as often as needed to keep the Lakehouse table up-to-date efficiently.
In Power BI Desktop, connect to the Lakehouse table using DirectLake mode for faster queries. You can still filter this data in Power Query or with DAX in a semantic model. To optimize performance, apply early filters in your Dataflow before writing to the Lakehouse and enable "Enhanced Compute Engine" in Dataflow Gen2 settings. Test the setup to confirm the error is resolved and load times are reduced, and check gateway logs in Power BI Service under Manage Gateways for stability.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.