Forum Discussion
Direct query limitations with the Snowflake connector
I have a report that connects to a Snowflake database via direct query, and a measure that calculates the median of a column. Because MEDIAN is a function that is supported in Snowflake, ideally the query would be folded back to the database, but I can see that Power BI is instead reading all the data back, then calculating the median locally. Because of this, it is a lot slower than it otherwise would be if it folded the query, and if there are more than one million rows to calculate the median from, it doesn't work at all. The documentation here mentions that "usually the median aggregate isn't supported by the underlying source", but does this mean it will not fold the query for any direct query sources, even though it supported by Snowflake in this case?
2 Replies
- lbendlin
Super User
It may be supported by Snowflake, but that doesn't mean it is included in Power Query's knowledge how to translate M code into Snowflake code. If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41
- jeffshieldsdev
Solution Sage
Update the Snowflake query to use native SQL, something like:
let Source = Value.NativeQuery( Snowflake.Databases("server", "warehouse", [Role="role"]){[Name="database"]}[Data], " sql ", null, [EnableFolding=true] ) in Sourceand use MEDIAN() function that way.
