Time Intelligence with Power Query

The Scenario

In DAX (Power Pivot’s Language), we have functions like DATEADD that can bring us the dates of a previous or next Year/month/day. We also have other functions like SAMEPERIODLASTYEAR that does just that, bring us the same period of dates but for last year. We also have functions such as TOTALMTD, TOTALQTD & TOTALYTD that can give us an accumulative value based on a year-to-date, quarter-to-date or even month-to-date.

This pattern’s goal is to show you how to create those functions, in an elegant and simple way, in Power Query with minimal coding and a great added value thanks to its user-friendly interface.

Download the Workbook

Our Goal

Goal TI

The previous image gives us an insight of what we want to accomplish. Note a few things:

  • We only have 1 source table
  • We need the following calculations
    • Get the previous year values
    • Calculate the Month-To-Date total of sales
      • maybe even get the previous year Month-to-Date value for that day/month combination

There are many ways to accomplish this and this pattern will show you a few examples of how to do it.

Preparing our Table

Before we jump into anything, we need to make sure of a few things so that our pattern can be implemented in an optimal way.

Here’s a little checklist of the things that we need to verify:

  • The Table has a Date Column
  • The Table has unique values in the Date Column
  • The Date Column name needs to be changed to “OrderDate”
    • this one is optional as you can tweak the code later to match your Date Column Name

If you’re having issues with the second bullet, where you need unique values for the date column, then you can use the following Pattern that can help you to group rows using the Date Column.

Group By or Summarize Data

Using Table Record navigation to get specific Records

One of the easiest ways to do time intelligence is to simply do Table navigation operations.

Let’s grab the data from the source table and then add a new custom column using this code:

try #"Changed Type"{[OrderDate=Date.AddYears([OrderDate],-1)]} otherwise null

Let’s understand this code;

try AND otherwise = are the equivalent of using IFERROR in Power Query where we try to do an operation where we hope it doesn’t fail, otherwise we throw a “null” for error values

#”Changed Type” = is the name of the previous step. This step is actually a table, so we’re using a table here

{ } = curly brackets define a list

[ ] = brackets define a record

Date.AddYears = is a Power Query function that adds or substrats a Year to a date value. Just like this function we also have others like Date.AddDays, Date.AddWeeks, Date.AddMonths and much more than can help you either move forward or backwards in time.

In short, what this is doing is basically scanning the #”Changed Type” table and searching row by row for the record that matches the crtieria.

The criteria is:

  • Give me the record of the previous year 🙂
    • eg: if in one row our date was 4/March/2014, then the scan would be searching for a 4/March/2013 (thanks to the Date.AddYears operation)

The result of this is actually a record that looks like this:


And what we need to do now is simply expand that column the get the Sales field, maybe apply some sorting and renaming as well and our end result will look like this one:


Replicate Power Pivot’s Filter Context with Table.SelectRows

In Power Pivot, using DAX, we have the abilty to create our own Custom Time Intelligence formulas by playing with the Filter Context.

We’re going to use that same concept of FILTER Context but in Power Query.

What is Filter Context? in a short and simple answer, it’s the filters that occur to a table.

We’re going to use a simple function that will save us a lot of time. To create this new function simply go to Power Query, create a Blank Query and, in the advanced editor, simply paste the following code:
Step = Table.SelectRows(MyTable, each Number.From([OrderDate]) <= Number.From(Input))

Click Done when you finish pasting the code and then you can call this query whatever you want. In our case, we chose to name it MyFunction.



What this function is doing is fairly simple.

  • It has 2 arguments or parameters.
  • The first paramater is the table where your data resides
  • The second parameter is the date column that will filter the table defined in the first parameter.

The operation that happens in the code, specifically this part:
Step = Table.SelectRows(MyTable, each Number.From([OrderDate]) <= Number.From(Input))

is a simple Filtering of a table. It filters the table using the column [OrderDate] from that table where it’ll bring only the records from that table where the [OrderDate] is less than or equal to the value from the second parameter.

Did you know that…Table.SelectRows is the same operation that happens when you click on the filter icon of a single column?



Applying the Function for a TotalMTD or Month-To-Date Analysis

Now that we have the function, we can go ahead and use it in a real scenario.

We want to create a Month-To-Date analysis, but we have several months in our main table. What can we do? We can group them!

Before we group them, we need some new columns that will help us group those dates. I’m choosing to add a Year and Month column numbers by simply clickin on the OrderDate column, going ot the Add Column Ribbon and simply let Power Query do all the hard work for me.

Here’s how that process and its results look like:


Now that we have the columns that can help us group our dates, let’s go ahead and group them like this:



And the result of that operation will give us a table that will look like this:



You’re going to notice that on the GroupedRows column we have now grouped all the sales by month by year.

What you see here is a nested table scenario where we have a table (or a column of tables) inside a table.

What we need to do is simply apply our function into that table. We need a add a new column into each one of those tables.

Click on the Add Custom Column button and add the following code to that formula:

=Table.AddColumn( [GroupedRows], "Cume", each
MyFunction(#"Grouped Rows"{[Year=[Year],Month=[Month]]}[GroupedRows], [OrderDate]))

You can probably see that we also did a table navigation in there in order to deal with the nested table scenario. The result of that should look like this:


Our Next Steps

Let’s go ahead and do the following:

  • Delete the GroupedRows Column
  • Expand the Custom Column to get only the following fields
    • OrderDate
    • Sales
    • Cume

The result should look like the following table:


Aggregate the Columns

You probably noticed that in each of those table values in the Cume field we only have a set of dates that are either equal or less to the one that we have in the OrderDate Column.

We’re used to Expanding a column, but we rarely use the Aggregate operation that Power Query gives us. Let’s try and aggregate the data from those columns like Power Query suggests.

Check out the following picture:


As you can see, we can do A LOT…and I mean A LOT of operations over those columns. We could do an average for all of those dates AVERAGEMTD, MAXMTD, MINMTD, MEDIANMTD or simply a count.

We’ve chosen to do a Sum of Sales and after doing some sorting and renaming, our result looks like this:


As you can see, our TotalMTD or Month-To-Date calculation is working correctly.

Final Scenario: Combine all the concepts learned

Let’s combine the concepts of the first query with the ones from the second one.

Let’s go ahead and click on the last query and do a reference on that one. I named my previous query Accumulate and that’s what you’ll see in your formula bar as your first step for this new query.

Now, instead of just doing that…let’s wrap that Accumulate within Table.Buffer to optimize performance. So our first step will have the following code in the formula bar:
= Table.Buffer(Accumulate)
Now, as you might already know, the name of our first step for this new query is Source. Let’s create a new custom column with the name Last Year MTD and add the following formula to that new custom column:
try Source{[OrderDate=Date.AddYears([OrderDate], -1)]}[MTD] otherwise null

And here’s the result:

Note: we’re extracting the value of a record and then navigating through it to an specific field, inside that record, called MTD and that’s why the result of that formula is a value and not a record. We could’ve left the code without the [MTD] and extract more fields from that record. Try it!

Power Query Patterns
Power Query Book

this work was shared 0 times

 Power Query Training

  (13 articles)

We are a team committed to delivering the best content for new and seasoned users of a tool that we believe in: Power Query. We firmly believe that this is THE best tool to be added to Microsoft Excel in a long time.