Queries from Access missing in PowerBI

[JonnoOon]

Hi All,

new BI user here.

I have a model which;

1) has a front-end excel model, where users can update data and assumptions

2) linked (live) into an access database, where a few queries are run to calculate final costs etc. 

3) queries are imported from access into PowerBI. 

Am noticing that quite a few of my queries in access are missing in BI.

I've done some preliminary searches, and they are not union queries ..

can't seem to figure out why it would be missing!

Any help you can offer would be much appreciated

Jon 

[Anonymous]

@JonnoOon,

Do you have any functions in the Access queries? Access queries will not appear in Power BI Desktop if you have functions, please remove functions from Access queries.

Regards,
Lydia

[JonnoOon]

Hi Lydia @Anonymous,

thanks so much;

can i clarify; even a basic sum function wouldn't be able to flow from access into BI?
My access database has no other functions than sums, or multiplying/dividing data in a query. 

Also, to test your solution I've tried making additional queries which simply select data from other queries which used sum functions etc.  These are still not appearing in BI. 

For example; 

qry_OEE_ByLine (image 1 on left) has a function at the end to calculate a metric by adding / dividing some values. this was not appearing in BI navigator.

I then made qry_lineOEEImport (image 2 on Right) then simply selects the outputs of that into a new query.

This still does not appear in BI navigator. 

 

Thanks very much

Jon 

[Anonymous]

@JonnoOon,

In my scenario, when I create a simple query that select some columns of a table in Access, the query appears in Power BI Desktop. Do you use the latest version of Power BI Dekstop(2.49.4831.521)?

And in your scenario, you can apply criteria(sum,mulityply, divide) in Power BI Desktop instead of Access.

Regards,
Lydia

[JonnoOon]

Hi @Anonymous

Thanks again for your support.

Does it work if the query selects columns from other queries which did have functions? 

I have tried making queries to pull from other queries but they are also not importing into BI. 

The issue is that all the work has already been done in Access (over 50 queries) and I was simply hoping to present it in BI rather than BI recalculating all of the outputs. 

2.49.4831.521 32-bit (August 2017) is my version - so looks like it is the latest one! 

Thanks so much Lydia

Jon

[Anonymous]

@JonnoOon,

Would you mind sharing me your Access database so that I can test?

Regards,
Lydia

[JonnoOon]

@Anonymous is there an email I can send to?

Thanks

Jon

[Anonymous]

@JonnoOon,

You can send me the Access database via Private Message.

Regards,
Lydia

[Anonymous]

@JonnoOon,

I get error message when trying to open datasheet view of you query in Access. As your post, I am not able to view thw Access queries in Power BI Desktop. 

However, when I try to create the following tables and queries in your Access database, I can view these queries in Power BI Desktop.



Regards,
Lydia

[JonnoOon]

Hi Lydia,

What exactly do you mean by "Datasheet view of queries in Access" - please excuse me, I'm fairly new to both Access and BI. 

Do you know the reason why / any solutions for why the Access queries are not available in PowerBI Desktop? 

Thanks,

Jon

[Anonymous]

@JonnoOon,

I click the following button. I suspect that some expressions that you use in  your queries are not supported in Power BI Desktop.


What is the result when you create the sample query as I do?

Regards,
Lydia

[JonnoOon]

Hi @Anonymous

I've tried replicating the exact query you made. 

This appears in BI navigator. 

Do you happen to know what functions specifically are not supported by BI? 

I've also tried creating queries that simply select columns from queries which were not appearing (e.g. in the access database BI_LineOEE simply selects columns from another query) - and these also are not appearing in BI. 

Thanks,

Jon 

[Anonymous]

@JonnoOon,

There is no official article and blog stating the supported Access functions in Power BI. But you can load your table to Power BI Desktop, and re-create these queries you need.

Regards,
Lydia

[JonnoOon]

Hi All,

new BI user here.

I have a model which;

1) has a front-end excel model, where users can update data and assumptions

2) linked (live) into an access database, where a few queries are run to calculate final costs etc. 

3) queries are imported from access into PowerBI. 

Am noticing that quite a few of my queries in access are missing in BI.

I've done some preliminary searches, and they are not union queries ..

can't seem to figure out why it would be missing!

Any help you can offer would be much appreciated

Jon 

[mohamedafi]

I have the same problem, please let me know if you found the solution