Forum Discussion
Power BI pulling through excluded rows from MS Access query
Hi.
I'm fairly new to Power BI and I'm experiencing some weird behaviour when importing data from MS Access. Can anybody shed any light on this?
Sorry for the long post but here goes...
I have data in MS Access which I manipulate into a dataset ready for Power BI via a sequence a queries in Access. The queries are fairly vanilla - they are a set of data manipulation steps on order lines such as adding master data, adding subtotals, etc. They do not use any VBA, nor do they use functions such as NZ(). Each row of output in the final query has a unique ID, taken from original order lines.
When I run the query in Access it returns c. 322,000 rows but when I import it into Power BI (with no data transformations), it pulls in about 1,200 additional rows...
I have viewed the data in Power BI desktop and the weird thing is that it is importing rows that were definitely excluded in Access (I can tell because the ID field shows values that do not exist in the final Access query).
On the other hand, if I make a table in Access with the results of the query and import that, everything is fine - no rogue rows.
For information, within Power BI I don't have any table joins that could be duplicating rows - and in any case, the additional rows are not duplicates: they are unique rows that were supposed to be excluded in the first place.
The only thing that I wonder might be causing problems is that some of the tables within Access have Boolean fields, but by the time I've run the sequence of queries, I've either removed the Boolean fields or coerced them into 0/1 values.
I'm at a loss as to the cause of the problem. I assume that the query is run by the MS JET engine (or whatever it's called nowadays) and then passed to Power BI, so is the problem likely to be with that, rather than Power BI?
Has anybody else every experienced something like this, or am I making classic beginner's mistake?
Thanks.
After more investigation it turns out it was a LIKE operator on a text field in the one of the Access queries.
Apparently Power BI doesn't recognise LIKE operators in MS Access if imported directly, but a workaround is to use an ODBC connection. See https://community.fabric.microsoft.com/t5/Desktop/Power-Bi-will-not-import-query-from-Access-with-quot-Like-quot/m-p/8038.
3 Replies
- rajendraongole1
Super User
Hi JonCW - You're definitely not making a "beginner's mistake"—this is a common issue with Access-to-Power BI imports. Since saving the query results as a table works fine, the issue is likely with how Power BI reinterprets the query.
Try forcing Power BI to respect the query exactly by:
Using a saved table instead of a query
Checking NULL and Boolean handling
Ensuring query folding does not modify results.
Please check and let me know if any support.
Thank you rajendraongole1.
I wanted to avoid using a saved table if at all possible, but kept it as my plan B.
It's working now, but I'm not sure exactly what I did to fix it! I think it was possibly changing all boolean fields into numeric type in Access, but I also changed one of the Access queries that was filtering rows using a clause on a string field into a table join instead.
By the way, I couldn't work out if query folding takes place when importing from Access. Do you know if it does and, if it does, how can it be explicity disabled in Power BI?
After more investigation it turns out it was a LIKE operator on a text field in the one of the Access queries.
Apparently Power BI doesn't recognise LIKE operators in MS Access if imported directly, but a workaround is to use an ODBC connection. See https://community.fabric.microsoft.com/t5/Desktop/Power-Bi-will-not-import-query-from-Access-with-quot-Like-quot/m-p/8038.
