cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Wout Regular Visitor
Regular Visitor

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

Accepted Solutions
Rich_P Regular Visitor
Regular Visitor

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

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

13 REPLIES 13
Super User
Super User

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

@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 a to be a Datanaut!
Wout Regular Visitor
Regular Visitor

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

Thanks for your reply. I cant find any differences.

Community Support Team
Community Support Team

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

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.
Wout Regular Visitor
Regular Visitor

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

Hi Dale,

 

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

 

 

Wout Regular Visitor
Regular Visitor

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

Kick

Community Support Team
Community Support Team

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

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.
Rich_P Regular Visitor
Regular Visitor

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

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

Wout Regular Visitor
Regular Visitor

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

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

 

nbailey Frequent Visitor
Frequent Visitor

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

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. Smiley Happy