Can't see Access queries in the Power Query get and transform for import/link?

[Maggie Barr]

Hello, and thank you in advance for helping if you can,
I have created and Access database, and I am trying to link to some of the queries from it in Excel's Power Query. In the get and transform, I can see the database, all of the linked tables, and one query I had created for a union query that is just a duplicate of one of the linked tables. However, none of the queries that have modified columns, calculated column, or data brought into them from other linked tables are visible. I am currently thinking that because the queries have "dependencies", but, being new to Access, I am not really sure what that means or if that is the problem. Does anyone here know why not all the queries form an Access database are visible to link to them in Power Query? I can always export the query and link to it, this I know, but if I could link directly to the query I need, it will prevent me from putting a "break" in the "chain" of analyses, thus allowing me to simply refresh/update everything in the appropriate order to update the analyses. Any advice or input would be appreciated, even if it is to tell me it can't be done so I can't quit searching.
Thank you for your time,
Maggie

 

[RoryA]

Do the queries you can't see use any Access-specific functions like Nz?

 

[Maggie Barr]

RoryA,
Yes, several of the queries have Nz functions, along with some filters and custom columns.
Maggie

 

[RoryA]

That's what causes the problem then. Nz only works inside the Access application. You'd need to replace that with an Iif(IsNull(...) type construction.

 

[Maggie Barr]

Ah ha! Thank you for this, I will see what I can do about trying to reformat the construction.
Best Wishes,
Maggie

 

[Maggie Barr]

RoryA,
THANK YOU! That worked for making all the queries, but the one I wanted, visible. The final query, a union query, wasn't visible, so I had to create a simple query of that one, bringing in all the fields, to make it a "table" query so it would then be visible. Had I not modified the Nz code though, it would have never been visible as the base query for everything could not be read by Excel's Power Query. Now it is visible, and I am good to go.
Best Wishes,
Maggie
Thought I would post what I changed in the function in case anyone stumbles upon this:
I changed the construction from Nz([GROUP IDENTIFIER],[SAMPLING EVENT IDENTIFIER]) to IIf([GROUP IDENTIFIER] Is Null,[SAMPLING EVENT IDENTIFIER],[GROUP IDENTIFIER])
Then I made a select query of my union query bringing in all the fields from that.

 

[Maggie Barr]

Okay, FYI for anyone interested,
Sorry for not trying it sooner, but I got it to work after all, just posting this in case anyone is interested.
I was able to make the union query visible in Excel PQ by creating a simple query from it, but it would not load properly in Excel's Power Query data model, it kept opening up all the linked tables that were part of all the Access queries though they weren't loaded to the data model in Excel and it couldn't load the Access query I needed to the data model, though I could see a preview of it/the data, very odd indeed. So, I used that query and did a make table query to get a physical table in the database. Fortunately, for now, since my entire database is built on links, it had enough space for the physical table.
Maggie