Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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.
@Wout is there a difference in the type of queries these missing queries are?
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?
Best Regards!
Dale
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
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |