Three Generations of the Same Task
By Dany Hoter, Datarails Solutions Architect
In parts I and II of this series, I reviewed different methods of importing data into Excel from an SQL command with some input parameters. The goal was to include parameter values coming from cells in Excel, with data automatically refreshed anytime the value of any parameter changes in the grid.
This week, I discuss how to accomplish this goal with the new kid on the block.
Using Power Query with Parameters
Power Query (PQ), aka Get & Transform in Excel 2016, supports parameters that can be used to filter data. I will not use this feature in my PQ example because it is not possible to link parameters to cells in Excel, and in general, changing a value of a parameter is too complicated for a regular business user.
Instead, I’ll describe a different mechanism to parametrize filtering in PQ which pulls the values for parameters from cells in Excel. Once this is the case, I can also automate the refresh anytime the cell value changes, similar to what I did here.
In this example, you can see a query that filters the DimDate table using two parameters to limit the calendar year and one parameter to filter the weekday.
Each parameter comes from a query that pulls a single value from a named range in Excel. To create such a query, you need to select the cell that contains the value, and create a new query from table/range.
Once in the query, you simply create a new step by using right-click/Drill Down from the actual value returned from the cell. This will return a value instead of a table, and thus will enable the result of the query to be used as a filter.
In the query that reads the actual table from SQL, you can use the name of the query as the value by which to filter. You first create the filter in the regular way:
In the formula bar, you can change the specific value to the query name that contains the selected day.
The last step is identical to the one described here. If I want to use three parameters, I could use this VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(“FromYear”), Target) Is Nothing Or Not Intersect(Range(“ToYear”), Target) Is Nothing Or Not Intersect(Range(“Day”), Target) Is Nothing Then
ActiveWorkbook.RefreshAll
End If
End Sub
The final result is here.
Another Variation with PQ
In Excel 2016, Power Query was integrated into Excel and renamed to Get & Transform. Consequently, the queries are accessible thru VBA and I can now change the query programmatically. So now I can manipulate the text of the query in a similar way to our example using an SQL query.
Why would I do it this way and not through parameters? In this manner, the kind of changes I can make to the query are more radical. I might choose to change from a column=parameter to column contains parameter. I can change between column=parameter to column <= parameter. So the values themselves can be dynamic, as well as other parts of the query.
Obviously, these kinds of changes can become more and more complicated, but are still possible. You can create the initial query with the PQ editor, which offers a lot of power.
The example with VBA changing the query is here. Instead of manipulating the connection command text, I change the query formula. The formula property contains the actual M script that is run during refresh.
The example is a simple one and I only change the value for a single parameter. In this case, this is the script:
Notice that I used the special value ~Day~ as the value for filtering the day. This is to make it easier to substitute the value with a real day name.
The VBA code used this time is :
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(“Day”), Target) Is Nothing Then
ActiveWorkbook.Queries(1).Formula = Application.WorksheetFunction.Substitute(ActiveWorkbook.Queries(1).Formula, “~Day~”, Range(“Day”))
ActiveWorkbook.RefreshAll
ActiveWorkbook.Queries(1).Formula = Application.WorksheetFunction.Substitute(ActiveWorkbook.Queries(1).Formula, Range(“Day”), “~Day~”)
End If
End Sub
I change the formula property of the query and then I refresh the connection. The object in the front is the query while the connection is rarely used in the context of PQ.
This concludes this mini-series about using parameters in SQL queries.
About the author:
Dany Hoter is a renowned Excel guru who works as a solutions architect at Datarails. Dany has over two decades’ experience working as an expert for the Microsoft Excel team, and his online Excel classes reach over one million students across the globe.