- Query Folding
- The Solution : Use JSON
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:
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 :
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.
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 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
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
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.