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.
Do these expensive transforms in the dataflow, not in the semantic model.
I moved those transformations to the dataflow. For now it is connecting without error but I said in the post the error isn't constant. This isn't the only file doing it, so I am hesitant to believe the applied steps are the issue. I am not seeing how a few expensive transformations would be causing the error "Expression.Error: The key didn't match any rows in the table.". Defintely be apart of the slow down, but not the cause, it's still extremely slow.
I saw this when I first got the error and have tried many times disconnecting and reconnecting, no luck.
If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .