Defining Configurable Settings for your Queries

(The information in this blog post is current as of the March 2014 release of Power Query.)

The Problem

A colleague sent me a copy of the Excel workbook they used for a blog post about using Power Query with Project Online (see the fantastic article: Creating burndown charts for Project using Power Pivot and Power Query). The workbook has a query which uses the Project OData API to pull down Task information to produce a “burndown” chart. The query was configured to pull down the last 90 days worth of data, and I wanted to make it a little more configurable. The original query looked like this:

The highlighted line shows the filter that limits the data to 90 days (#duration(90, 0, 0, 0)). To change the value, we’d need to modify the formula in the Power Query editor (or open up the Advanced Editor and change it there). Making the change isn’t hard, but I wanted to make the workbook a bit more flexible so that anyone I shared it with could set the value without having to understand the script.

Using a Configuration Query

A Power Query query can reference other queries within the current workbook. We’re going to use this functionality to create a query that returns the settings we want, and then reference it from other queries to avoid hard coding anything. The steps will be:

  1. Create an Excel table that contains the settings we want to reference
  2. Create a query (named Settings) that reads the table
  3. Replace the hardcoded values with a reference to the Settings query

The Excel table is simple – a header row and a single row of data. The table should have a column for each setting we want to reference by name. In this case we have a single column the number of days we want the Project Online query to grab – we’ll call it DaysToKeep.

image

Next we create a query using the From Table button on the Power Query ribbon.

image

We’ll apply the following transformation steps:

  • Set the right data types
  • Convert the table to Records using the Table.ToRecords function
  • Select the first (and only) record
  • Disable loading the query (i.e. deselect “Load to Worksheet”)
  • Name the query something easy to remember (ex. Settings)

The query looks like this:

This gives us a query that returns a Record, which lets us refer to each field by name.

image

After saving the query, we’ll be able to use Settings[DaysToKeep] in any other query within our workbook, and it will evaluate to “30” at runtime. We can test this out by created a new query (Power Query –> From Other Data Sources –> Blank Query), and typing it into the formula bar.

image

Going back to the original burndown query, we can now replace the hard coded 90 value with the reference to the DaysToKeep setting.

After we change the query to use the Settings value, we’ll receive a prompt from Power Query’s Formula Firewall.

image

This prompt occurs because we are using the DaysToKeep value in a filter statement, which ends up getting folded into the query sent to the OData feed. The firewall prompt is meant to prevent unintentional data disclosure. In this case we’re not sending private information to the other data source (i.e. the number 30), but it would be a concern if we were joining a private data set (i.e. a customer list) with a public web service. Clicking Continue brings up the Privacy levels dialog.

image

From here we can specify that the OData feed has a privacy level of Organizational, and the settings from the Current Workbook can be considered Public (since they aren’t sensitive in any way). This satisfies the security requirements for the Power Query formula firewall, and lets the engine fold the values into the query as expected. Note, if the privacy levels didn’t allow folding (i.e. Current Workbook is set to Private, and the OData feed is Public), Power Query would do the filtering locally (in memory), rather than including the filter in the query. Once the privacy levels have been set, our query runs successfully.

image

After clicking apply, the query refreshes and brings a total of 110 rows into the data model.

image

To pull more data, we can simply change the value in the settings table, and refresh the query.

image

 

2 thoughts on “Defining Configurable Settings for your Queries”

  1. I think you can make it even easier – you can pass the number of days to keep this way:

    let CreateTable = (DaysToKeep as number) as table =>
    let
    Source = OData.Feed("https://<site&gt;.sharepoint.com/<site>/_api/projectdata"),
    TaskTimephasedDataSet1 = Source{[Name="TaskTimephasedDataSet"]}[Data],
    CurrentTasks = Table.SelectRows(TaskTimephasedDataSet1, each [TimeByDay] < (DateTime.FixedLocalNow() )),
    OldestTasks = Table.SelectRows(CurrentTasks, each [TimeByDay] >= (DateTime.FixedLocalNow() – #duration(DaysToKeep, 0, 0, 0))),
    RemovedOtherColumns = Table.SelectColumns(OldestTasks,{"ProjectId", "TimeByDay", "ProjectName", "TaskActualWork", "TaskWork"}),
    ReorderedColumns = Table.ReorderColumns(RemovedOtherColumns,{"ProjectId", "ProjectName", "TimeByDay", "TaskActualWork", "TaskWork"})
    in
    ReorderedColumns
    in
    CreateTable

Leave a Reply

Your email address will not be published. Required fields are marked *