How to write a Snowflake SELECT statement query in Advance Editor from powerBi

Question

I am trying to repoint the data Source of a power Bi dashboard from SQL to Snowflake.

So far, for most of the tables worked. However, I got one table on which I got the following error when changing the data source:

Expression.Error: 'Query' are not valid Snowflake options. Valid options are: 'ConnectionTimeout, CommandTimeout, CreateNavigationProperties, Role'

This specific query ( from Advance Editor in PowerBi) contains a simple select and it looks as follows:

let
    Source =Snowflake.Databases("serverabc", "abc", [Query="SELECT DateLabel, SnapshotDate, Future, Latest#(lf)FROM Xtable#(lf)WHERE DateLabel IS NOT NULL#(lf)GROUP BY DateLabel, SnapshotDate, Future, Latest", CreateNavigationProperties=false]),
    #"Filtered Rows" = Table.SelectRows(Source, each true)
in
    #"Filtered Rows"

The select statement works in both SQL and Snowflake but I am having difficulties on how to translate this in Power BI as well.

Thank you in advance

Thanks so much,Lukasz, for the insight. I've brought the table in pbi and I've adapted the select statement in M code. It worked! — Mirela Pipirig
Great to hear it. How does accepting an answer work? The updated connector (June 2021) now allows to pefrom custom query. Please check my updated answer. — Lukasz Szozda

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-04-17T12:35:32

EDIT:

PowerBI June

Snowflake (updated connector)

We are adding the highly demanded Custom SQL support for the Snowflake connector. Like the SQL connector, this will let you input a Snowflake native query and then build reports on top of it. This will work with both Import and Direct Query mode.

https://powerbiblogscdn.azureedge.net/wp-content/uploads/2021/06/snowflake_update.png

Expression.Error: 'Query' are not valid Snowflake options. Valid options are: 'ConnectionTimeout, CommandTimeout, CreateNavigationProperties, Role'

It seems that the previous source was supporting "custom query".

Sql.Database
function (server as text, database as text, optional options as nullable record) as table
Query : A native SQL query used to retrieve data. If the query produces multiple result sets, only the first will be returned.

PowerBI connector to Snowflake does not support such option:

Snowflake.Databases
function (server as text, warehouse as text, optional options as nullable record) as table
options, may be specified to control the following options:

ConnectionTimeout: The number of seconds to wait for network responses from Snowflake.

CommandTimeout: The number of seconds to wait for a query to execute.

There is active ticket: Snowflake connector -> add SQL statement as optional.

Possible workarounds:

Create a view in Snowflake that wraps the query and use it instead
Access the table content and perform the filtering/aggregation in PowerQuery

How to write a Snowflake SELECT statement query in Advance Editor from powerBi

1 Answers

EDIT: