Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wout
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
ruut
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.

 

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

vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.