Query Folding

In a recent video by Guy In A Cube, Adam showed a great trick to pass a list to the where clause in a SQL statement. I want to show another approach which is more parametric and can fold. It does come with one drawback that I will share. Watch Adam's video first:

Query Folding Using Value.NativeQuery()

It's known that instead of pasting query in the SQL server connection dialog box like below, you can use the same SQL statement in Value.NativeQuery() to make it foldable. This has the advantage of potentially speeding up the refresh times by offloading all the processing to the server instead of the gateway. I highly recommend reading this blog by Chris Webb, which is how I learned about this function. I have also added few more resources about Query Folding at the end of the blog, please check them out.

This will not fold :

Using Value.NativeQuery()

This will fold:

What's not generally known is that you can pass parameters to the Value.NativeQuery() using the optional third argument. Again, I learned about it from Chris' blog here. I modified the SQL statement and passed a parameter @Date by using the third optional argument as shown below. You can use as many parameters as you like. The query is still foldable.

Note: Be sure to set [EnableFolding=true] to fold subsequent query steps. If you don’t specify this, your initial query will fold but not the queries following the initial query

But What About Lists ?

Good question. If we try to pass a list in a where clause like Adam did, however, it will not work. See below. The parameters only work for single values.

The Solution : Use JSON

The solution to this is using OPENJSON format to pass the values. I have not tested it against all the foldable sources. It certainly works with SQL Server and Synapse Analytics. I found this solution last year on a forum somewhere and I can't find the source anymore unfortunately. If anyone knows it, please let me know so I can attribute to the original author.

Create the parameter as a JSON object and read it in SQl statement using OPENJSON

What If You Have An Existing List ?

If you have an existing list that's derived from a column, you can use Text.Combine(), like Adam did, and pass it to the JSON parameter we created. If you want to pass numbers to the where clause like I did above, here are the steps to pass your list named _mylist as a parameter:

First convert the numbers to text, you can use Number.ToText(). Easier to just convert the column to text before creating the list
Concatenate the list: "[" & Text.Combine(_mylist,",") & "]"
Now you can specify this list as a parameter directly in the Value.NativeQuery() parameters option

The only drawback here is the automatically generated SQL query does not show the values passed to IN. But it folds, that's what matters.

Resources

Other than Chris Webb's blog, here are couple more resources on Query Folding:

30DQUERY Challenge

If you want to improve your M and learn the techniques to make queries foldable, I highly recommend participating in Alex Powers' #30DQUERY challenge.

Ben Gribaudo's Blog

Ben's blog is the most comprehensive resource on Query Folding. If you want to get deeper understanding of query folding and M in general, you have to read Ben's blog.

Watch How Power Query Thinks by Ben