cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

View solution in original post

13 REPLIES 13
vanessafvg Super Contributor
Super Contributor

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.

v-jiascu-msft Super Contributor
Super Contributor

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

v-jiascu-msft Super Contributor
Super Contributor

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

View solution in original post

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. 🙂

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 165 members 1,897 guests
Please welcome our newest community members: