Monthly Archives: February 2014

Excel 2013: Power Query OmniQueries. Dynamic And Refreshable Connections To The Data Model

2 Replies

Inspired by Chris Webb’s recent post on loading Power Query (PQ) M code from text files

http://cwebbbi.wordpress.com/2014/02/04/loading-power-query-m-code-from-text-files/

and by Dick Moffat’s articles on refreshable Data Model connections in Excel 2013

http://dmoffat.wordpress.com/2013/08/14/building-a-flexible-and-efficient-client-side-powerpivot-solution-in-excel-2013-2/

and

http://dmoffat.wordpress.com/2013/08/21/how-you-can-change-to-another-connection-type-in-powerpivot/

I have come up with a method to make PQ queries to the Excel 2013 Data Model dynamic and refreshable.

[BTW, in order to better understand this post, you might want to read the referenced articles at the links shown above before proceeding, because I sense that this article is not my best literary work]

Chris discovered that by using the M code Expression.Evaluate function, a PQ script stored in a text file can be read by PQ and applied as a query. I realized that if I could create a way for the text filename string to be picked from a list in Excel and read into PQ, I would have a dynamic and refreshable way to feed any PQ query into a single connection as per Dick’s example with native Excel queries.

To my knowledge, the only information that can be read from an Excel workbook by PQ resides in a table, so I made a single cell table (Table3) as shown below.

The PQ query that uses the text filename string is shown below.

PQ_Refresh1

Let

// Get filename from an Excel single cell table

QSource = Excel.CurrentWorkbook(){[Name=”Table3″]}[Content],

QText = QSource{0}[DynCell],

//Load M code from text file

Source = Text.FromBinary(File.Contents(QText)),

//Evaluate the code from the file as an M expression

EvaluatedExpression = Expression.Evaluate(Source, #shared)

in

EvaluatedExpression

By applying data validation to the single cell in Table3, the value in that cell can be changed based on the selection of a filename from the validation list (shown in column T in the Figure). In this example, cell P4 is the cell where the data (list) validation is applied. The goal is to trigger a calculate event when the value in that cell is changed, which will run the following procedure.

Private Sub Worksheet_Calculate()

If ActiveCell = Range(“p4″) Then

For Each conn In ThisWorkbook.Connections

conn.Refresh

Next

End If

End Sub

Unfortunately, changing a value in a cell based on a data validation list does not trigger a calculation of the worksheet. However, there is a kludgy trick that can be used to cause a calculation to occur. By placing the following formula in a cell (in this case F1, as shown in Fig. 1), the event procedure will run when the validation list is used in P4.

=COUNTA(P1:P5)

So, either of the two scripts stored in text files are run by selecting the filename string in P4 which ends up in  QText in the main query. Essentially the same PQ script used in Chris’s post was used in this example (Table4, in Figure) for DynamicQuery1.txt. For the query DynamicQuery2.txt, I changed script slightly to:

let

Source = Excel.CurrentWorkbook(){[Name=”Table4”]}[Content],

GroupedRows = Table.Group(Source, {}, {{“Average of Sales”, each List.Average([Sales]), type number}}

in

GroupedRows

The process works equally well whether the query is loaded on a worksheet or directly into the Data Model. Since this refresh scenario is in a workbook designed to have only one connection, a generic macro is used in the refresh process.  In more complex workbooks, the VBA procedure can obviously be crafted to selectively refresh only the “OmniQueries” present in the workbook.

Finally, although I have not tested this, it appears likely that this same methodology can be used with SQL statements in PQ in the manner described in this Microsoft article:

http://office.microsoft.com/en-us/excel-help/import-data-from-database-using-native-database-query-HA104123758.aspx

The SQL.Database M function shown in the figure at this link uses a SQL query string, which can likely be called by the Expression.Evaluate function using the same process described in this post.

Let me know if you find this technique useful, and thanks again to Chris and Dick for paving the road.