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:

PreviousYear

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:

FinalResultTableNavigation

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:
(MyTable,Input)=>
let
Step = Table.SelectRows(MyTable, each Number.From([OrderDate]) <= Number.From(Input))
in
Step

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.

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?

Filtering

 

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:

CalculatedFields

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

GroupedRows

 

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

TableGrouped

 

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:

CumeColumn

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:

Priortoaggegatecume

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:

Aggregate

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:

TotalMTD

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:

FinalResult
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
Power Query Workshop

this work was shared 0 times
 800
About

 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.

  • Raymond Wong

    In your example code, “(MyTable, Input)” line is missing the “=>” operator

  • Martin Short

    Hi Ken & Miguel,

    I may be taking completely the wrong direction but I’m trying to do something similar in order to calculate monthly
    values from YTD values (ie Jul YTD Jun YTD = Jul Month). I have done this in Power Pivot but for
    different reasons want to do this in Power Query. However, if I’ve understood correctly (and I’ve
    tried) Date.Addmonths only works when you have unique dates.

    I want to use Date.Add (or similar) as it automates the
    production of the monthly values prior to pivoting – otherwise I have to manually
    update the query each month. The problem
    I have is that using simplified Trial balance data I have multiple lines for
    the same date (ie Ac No, Dimension1, Dimension2, value, etc). I think there may be a way to do this in PQ for
    example if it could create ‘virtual table’ which can then use Date.Add for those
    rows that match but my M simply isn’t up to this standard. Any ideas or suggestions you have would be
    greatly appreciated.

    PS love the book-it never leaves my side (wife’s not too impressed though)!

    • Hey Martin, Thanks for the kind words!

      I’m not completely following your scenario. Do you think that you could post your question on

      http://www.excelguru.ca/forums/forumdisplay.php?19-Power-Query-(Get-amp-Transform) with a workbook and some dummy data so we can work with?
      The Date.Add____ can work without unique dates, but the pattern here does need a set of unique dates and that’s where the ‘Group By’ feature comes in handy.
      Send us the link to the thread once you post it. Ken and I are always keeping an eye on the forums.
      Best!

      • Martin Short

        Ok, I’ll see what I can do in the next few days. I’ve now actually managed to achieve what I wanted to do by using a combination of sorting (so that rows with similar dimension values are adjacent), grouping, concatenating & then referring to the previous row to pull back a value where the details match but are from a different time period. I can then subtract Jun YTD from Jul YTD to give me Jul Month at account row level. However, the query is incredibly slow & therefore unusable for practical purposes. I would therefore still be interested to know if there is a better technique out there to do the same thing.

        • I’d agree! nothing compares to the engine that DAX uses. The reality is that with Power Query its a case by case scenario but certain techniques would greatly optimize the query by using the Table.Buffer or List.Buffer functions

x