cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Power BI | Access data source - doesn't show all the queries

Hi Forum,

 

When i open my Access database i see 20 queries. And when i connect the Access file to Power BI i only see 16 queries..

 

Can anyone help me?

 

Thanks.

 

 

1 ACCEPTED SOLUTION

This has happened to me tons of times. Turned out that i was using the NZ() function in my queries. If you are using that very common function, replace it with an IIF() function. That should do the trick.

 

EX:

IIF([fld] IS NULL , 0, [fld]) if you are testing a numeric value

or

IIF([fld] IS NULL , "", [fld]) if you are testing a string value

View solution in original post

13 REPLIES 13
Frequent Visitor

Lets assume your query which is invisble in Power BI is named Query1. Now create a new query in MS Access named Query2 which contains:

 

SELECT [Query1].*
FROM Query1;

Save the query and in my case Query2 was visible in PowerBI.

 

Frequent Visitor

Lets assume your query which is invisble in Power BI is named Query1. Now create a new query in MS Access named Query2 which contains:

SELECT [Query1].*
FROM Query1;

Save the query and in my case Query2 was visible in PowerBI.

Super User I
Super User I

@Wout is there a difference in the type of queries these missing  queries are?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for your reply. I cant find any differences.

Hi @Wout

 

It’s a little strange. Let’s check it step by step. It seems that “Forms” and “Reports” can’t be imported. Could you check on your scenario please?

 

 

Power BI Access data source - doesnt show all the queries.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,

 

Thanks. They are 100% queries, just like the others.

 

 

Kick

Hi @Wout,

 

Did you use a PlainText function in your access query? Maybe this post could help you: Missing-MS-Access-tables-queries-in-the-Navigator

 

Best Regards!

Dale 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This has happened to me tons of times. Turned out that i was using the NZ() function in my queries. If you are using that very common function, replace it with an IIF() function. That should do the trick.

 

EX:

IIF([fld] IS NULL , 0, [fld]) if you are testing a numeric value

or

IIF([fld] IS NULL , "", [fld]) if you are testing a string value

View solution in original post

@Rich_P, one of all the missing queries was using the NZ() function and their were linked to each other. Thanks for your help 🙂

 

Problem solved.

Thanks for your replies @Rich_P & @v-jiascu-msftbut i dont use the NZ() and Plaintext function.

 

There are certain queries and functions that when used in Access renders the query unviewable/useable in PowerBI although I've never found a definitive list of what is not compatible.  I recently modified a query and it is no longer viewable in PowerBI, but I'm still troubleshooting to figure out exactly what it doesn't like (was looking on the Community for some inspiration when I came across your post).  Thus far I have figured out that removing reference to a Union Query makes it reappear in PowerBI (but I have at least one other query that references a Union Query so that in and of itself is not the issue).  My suggestion is to follow a "trial and error" approach (copy the query and remove different components - fields using formulas, references to other queries, etc.) until you can narrow down the issue.

 

Good luck. 🙂

I have no explanation, but I have resolved my issue.  After narrowing it down to the Union Query and unable to identify any reason why that was causing a problem, I used the Union Query to make a table.  Then I was able to reference the newly created table (output of the Union Query) rather than the Union Query itself and the original query that disappeared magically reappeared in PowerBI.

 

I had used almost an identical process with a Union Query previously with no issues with visibility in PowerBI - the only difference is that in the recent example the Union Query returned many more rows than the previous.  So maybe it was some sort of internal memory thing to execute all the related queries.

 

Whatever the cause...now I can at least move on with my life...

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors