SnowFlake & PowerBI "native queries aren't support by this value"

Question

Quick Note, I have reviewed these threads and they do not fix my issue:

( Outdated info, see documentation below ) Access Snowflake query results through PowerBI

( I would expect this to fix my issue, but it does not ) How to write a Snowflake SELECT statement query in Advance Editor from powerBi

Hi All,

When attempting to query snowflake with a native query, I get this error:

These are the parameters:

I have verified the credentials / tables /  databases / schemas are correct by connecting to directly to one table at a time, but simple queries like the screenshot and complex queries all return this message "native queries aren't support by this value".

I know this is a new feature ( June 2021 ) and I have read the documentation here: https://docs.microsoft.com/en-us/power-query/connectors/snowflake#connect-using-advanced-options

EDIT:

I have tried the following query formats:

SELECT * FROM "MyDatabase".PUBLIC.ITEMSTABLE

SELECT * FROM "MyDatabase"."PUBLIC".ITEMSTABLE

SELECT * FROM "MyDatabase"."PUBLIC"."ITEMSTABLE"

I believe that this may be due to my MyDatabase being case sensitive and PowerBI stripping the quotes around it in the query.

In snowflake, this query succeeds while the same query in PowerBI fails:

SELECT * FROM "MyDatabase".PUBLIC.ITEMSTABLE

Issue opened with Microsoft here:

https://community.powerbi.com/t5/Issues/Unable-to-query-case-sensitive-Snowflake-tables/idc-p/2030983

Any help is appreciated.

Answer 1

It has been confirmed by a Microsoft ticket that my issue was that I had a case sensitive database name. The solution from MS was to... Not have a case sensitive DB name.

Answer 2

Most likely the query provided in message box is terminated with semicolon. It should be removed from the source query:

---

And actual query sent to Snowflake:

As we can see it is wrapped with outer query so any kind of input that makes the full query invalid one will error out.

Answer 3

I had similar issue with Native query written using Dataverse as Datasource. PowerBI Refresh was successful on the power bi desktop but refresh was failing on powerbi server. It was fixed for me when I have appended [EnableFolding=false] keyword in the native query. I have attached the screenshot for a reference of what i have modified. Hope this will help someone

enter image description here

Regards, Mohith