Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SHOOKANSON
Advocate I
Advocate I

Dataflow Refresh Error

Hi all,

 

I have just started using data flows for the first time. When I first load my data into Power Query, it loads fine. When I go to refresh for the first time, I am getting an error: "The key matched more than one row in the table." This isn't making much sense to me because the field identified as the key is a unique field. I am hoping I am just missing a step somewhere? I am connecting to an IBM Db2 database. I have rechecked my credentials multiple times. Please help!

 

SHOOKANSON_0-1643389469256.png

 

7 REPLIES 7
v-xiaotang
Community Support
Community Support

Hi @SHOOKANSON 

Have you solved this question? I also find an article about this error, see https://blog.crossjoin.co.uk/2020/04/06/understanding-the-the-key-didnt-match-any-rows-in-the-table-...

 

If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.❤️

If you need more help, please let me know.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

AnkitKukreja
Super User
Super User

Hi! @SHOOKANSON 

 

You've have make your text upper or lower case and then try to remove duplicates. Also you can use count rows options under transform data to see if you data actually removes duplicates or not.

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
edhans
Super User
Super User

Power Query is case sensitive, and DAX is not. So if you have the following in a table:
Apple
APPLE
Removing distinct on those will leave two records. DAX though sees that as a duplicate and will not let it load if that is the primary key in a 1 to many relationship.

You may also have a blank in there. There can be no nulls or blanks in the primary field for a 1 to many.

The fix to case sensitivity is convert that field to lower case (or upper case) by right-clicking on it, Transform, Lower. Then remove dupes.

To remove the blanks, click on the filter dropdown for the field and Remove Empty. It gets rid of nulls and blanks (shown as "") 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Ok so my primary key is numeric so case sensitivity is not an issue. There are no nulls as this is the history sequence field pulling from our ERP. I am going to keep working with it today but any other suggestions would be great! 

I would not assume null isn't the issue as I've seen errors in ERP databases. 

In Power BI, remove the filter relationship so it no longer 1 to many. Then bring the table in. Then look at that field. Is it 100% unique, or are their dupes? You can also filter and it will show blanks. Either will show you what you need to re-create the 1 to many relationship



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

When I pull the same table into Power BI Desktop, I am not finding any nulls or dupes. When doing dataflows, I am just pulling in one table. When I go to create an ML model with the data is when I am getting the error. I am pulling my hair out trying to figure it out. 

Kinda stumped here, difficult to know without seeing the data myself. You seem to have done all of the right troubleshooting steps. 

When you say it is a number, an integer? The only other thing I can think of is you are pulling in a decimal or currency format and there is some rounding going on causing an issue, but that would be really unusual for a key. Still, should definitely be set to Whole Number. Often, numerical values from Dataflows get typed as Decimal and have to be changed back to Whole or Currency.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors