Query folding and dynamic parameters in SQL Queries in Power Query

Before you start reading this article, I recommend you to go through very good post on Ken Puls’s blog – Pass Parameters to SQL Queries.

If it is fine for you to use native queries to database then most probably scenario described below is not so interesting for you.

Here I’ll talk about workaround allowing to use query folding and pass parameters in Power Query without building of native SQL query.

Level: intermediate

Task definition

Generate N workbooks with data models populated with limited data scope.

Then each workbook will be shared with certain group of people, that allowed to see only relevant set of data (e.g. department OPEX of particular subsidiary).

This is dictated by fact, that if you share Excel workbook with someone – user immediately has access to all info inside data model. So, I cannot create one common data model and hide slicer somewhere with activated department. I must prepare separated workbooks.

Conditions

Data: Table with millions of records in SQL database – main data source for report (query folding required for obvious reasons)

Tools: Excel + Power Query + PowerPivot

Support data: table in Excel, defining datasets – data scopes, let’s call it Control Table

Sample of control table – easy way to define scopes

For demo I’ll use AdventureWorksDW2012, although it doesn’t contain a lot of data.

In words:

Resulting workbook with data scope [FR VP] (for France vice president by meaning) must contain only financial data of

organization 11 (France),

departments 2, 3, and 4 (Executive General and Administration, Inventory Management, Manufacturing)

SQL query can be composed easily:

But the question is how to replicate this in Power Query and keep query folding (execution of filtration on server’s side).

First of all, let’s see at M code when we do such filtration manually.

Connect to SQL server and filter data in query editor

I refreshed preview in query editor and checked trace in SQL Profiler to be sure that query folding takes place

Check auto-generated M code

Highlighted row – is what we want to generate dynamically using our Control Table.

Here for the rescue comes function

Expression.Evaluate( “each ” & #”Where Clause String” ) )

So, our resulting filtering step will look like

#”Filtered Rows” = Table.SelectRows(#”Removed Other Columns”, Expression.Evaluate( “each ” & #”Where Clause String” ) )

Where #”Where Clause String” must contain all necessary criteria defined for selected SCOPE.

Let’s consider complex case, when one of criteria can be omitted

Imagine that someone is responsible for that set of organizations and departments.

Idea: transform initial table narrowed to SCOPE into following table with lists

Then

Rows of this table must be combined with ” or ” operator, and columns with ” and “. In consideration of null in some cells.

Hence, consider three situations

Both fields are filtered – such rows will give us pairs like: ( [OrganizationKey] =11 and [DepartmentGroupKey] =6 )

Only Organization – like: ( [OrganizationKey] =13 )

Only Department – like: ( [DepartmentGroupKey] =4 )

Visualized:

Finally, combine using ” or “

Yes, probably, resulting query is not optimal, SQL Profiler shows that check on the same organization is going several times

However, desired query folding takes place, and solution is straightforward. For some cases it is enough.

Probably, SQL server engine can even handle such queries in a smart way.

Nevertheless, solution can be improved:

Rows of expanded table can be grouped by Organization (or in other situation by field that often is used in criteria).

For each group we can build own one-row text criteria

This can be achieved with help of loop through distinct list of Organization values.

Loop can be performed using List.Generate.

Each step of loop:

Filter table by Organization

Combine Department criteria

Finally, check “where clause” in trace

Quite complex to read and check it. Not every pair concluded in brackets, unfortunately.

——– UPDATE ——–

Following advice from Chris Webb I reached more accurate query.

For example, such string of M code

Table.SelectRows(#”Removed Other Columns”, each [OrganizationKey]=11 and List.Contains( {6,7}, [DepartmentGroupKey] ) )

will be transformed into statement

where ( [_].[OrganizationKey] = 11 and ( [_].[DepartmentGroupKey] in (6, 7) ) )

To achieve this, I slightly changed my function GetCriterias to go from multiple “or” to “in”

Workbook with full code can be found here (updated).

Conclusion

Firstly, again Expression.Evaluate shows it’s power. Build a string and transform it into command, build flexible solutions.

Secondly, having such list of scopes in Control Table, located in “master Excel-workbook with data model”, we can run a macro or script, which will go through all scopes and save resulting data models as separate workbooks. Using script we can even launch refresh of several scopes in parallel – in separate Excel applications.

Almost one year ago I came to idea to create such script, that can be scheduled with Windows Task Scheduler. This idea brought me to creation of Power Refresh – self-service solution written in Visual Basic (Excel-people language). More about its current version and plans in next post.

May the Force of Power Query be with you!