Adding query in PowerBi Dataflow

[Anonymous]

I want to create a dataflow by connecting to Snowflake. Is there a way to directly pull the data using a snowflake query ?

When i connect to snowflake database, it directly gives a table without letting me to filter a particular set of data.

[BA_Pete]

Hi @Anonymous ,

As Snowflake is essentially a foldable source, any basic operations you perform on the query in your Dataflow (basic row filters etc.) should be folded to the source, so you're not actually bringing in the whole table then filtering back again, the Dataflow will amend the query actually sent to the source to include your filters.

Pete

[Anonymous]

Hi @BA_Pete : Actually I want to use a SQL rank function in snowflake query to filter the data which is not possible in PowerBI query. What I want to know is , is there a way to add a custom step which will include the sql query ?

Also, what will be syntax to add the query ?

[BA_Pete]

To connect using a custom SQL query, you need to use the advanced options in the connction dialog.

When you go to connect to a Snowflake source, you should get a dialog something like this where you can expand the advanced options and enter your own SQL:

Pete

[Anonymous]

Unfortunately the Snowflake Connection dialog does not have an option to enter a query.

[DokDok]

i have the exact same display, different to what PETE shows.

no box to put in SQL Statement. Aug 2024

[BA_Pete]

Hi @DokDok ,

The SQL statement option only seems to appear in the Desktop connection.

My suggestion was to use Desktop to create the query required, then use Advanced Editor to copy the M code and paste that into Advanced Editor in a blank query in a Dataflow. This works for other users.

Pete

[BA_Pete]

What version of PBI Desktop are you using? The updated Snowflake connector was added in the June 21 release, as per this blog from Snowflake:

https://www.snowflake.com/blog/custom-sql-now-supported-in-the-snowflake-connector-for-power-bi/ 

And per this article on MS Learn:

https://learn.microsoft.com/en-us/power-query/native-database-query 

Pete

[Anonymous]

The question refers to Power BI dataflow, not to PowerQuery in Power BI desktop. In a dataflow there isn't this option for some reason

[BA_Pete]

Then just do it in Desktop and copy the M code into a blank Dataflow query.

Pete

[kiomorita]

That worked for me.

Thanks @BA_Pete  🚀