Forum Discussion
Power Query is Missing Some Values on Import, Causing IDs to Map to the Wrong Records
Hi. I have a Power Query / Excel issue that I just cannot find a solution to, or a similar problem online.
I have a third party cloud based database which I am querying into Excel via an OData connection. The source database contains some duplicate names. As this is real company data, I've created a little demo (screenshot below) to explain the problem.
So say I had two companies called Bob's Buns in my original data set. Power Query does not pull the second instance of the name "Bob's Buns", which causes all the names below to line up with the wrong IDs. So now "12" is linked to "Clare's Cakes".
You'd think I could just make sure that no two companies in my database have exactly the same name and this would fix the issue, but it doesn't. When I pull the data again after fixing the duplicates, it STILL results in this misalignment error. In fact if I renamed Bob's Buns (ID 11) to "Bob's Buns Bristol", and Bob's Buns (12) to "Bob's Buns Birmingham", Power Query still returns exactly the same result you can see under "What Power Query Returns" below.
It's definitely the duplication causing the error, as there were a few instances of this within the database, and the same thing happened.
I have done a static export to Excel from the source database, and the results line up correctly with their IDs. However, when I used OData & Power Query, I get this infuriating error.
I hope I have explained myself OK! Help very much appreciated!
2 Replies
Would suggest trying this:
- Check for Filtering or Aggregation in Power Query – Sometimes, Power Query automatically removes duplicates if it assumes they are redundant. Ensure that no implicit filtering or grouping is happening.
- Verify the OData Query – If possible, check the raw OData query being sent to the database. Some OData services may not return duplicate records due to backend constraints.
- Use a Unique Identifier – Instead of relying on company names, try pulling data using a unique ID column to ensure all records are retrieved correctly.
- Expand the Query Options – Some users have resolved similar issues by modifying the query options in Power Query to ensure all records are included.
- Test with a Different Connection Method – Since your static export works fine, you might try using a different method, such as direct SQL queries or API-based retrieval.
Thank you so much for your reply. I tried most of these things and got nowhere. Then I discovered that there were two virtually identical tables in the database, and of course I was pulling the wrong one! (I was looking for companies; the table was called Companies, what was I supposed to think - I do feel slightly embarrassed for not working it out though!) Thank you so much for taking the time though, I appreciate it.
