Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Allocation in Power Query, Part 2

with 2 comments

Last week’s post on allocation in Power Query caused quite a lot of interest, so I thought I would follow it up with a post that deals with a slightly more advanced (and more realistic) scenario: what happens if the contracts you are working with don’t all start on the same date?

Here’s the table of data that is the starting point for my examples:

image

I’ve made two changes:

  • I’ve added a contract name to serve as a primary key so I can uniquely identify each contract in the table. Several people asked me why I added index columns to my tables after my last post and this is why: without a way of uniquely identifying contracts I might end up aggregating values for two different contracts that happen to have the same number of months, contract amount and start date.
  • I’ve added a contract start date column which contains the date that the contract starts on, which is always the first day of a month.

Now let’s imagine that you want to make each monthly payment on the last day of the month. You need to take each contact and, for each monthly payment generate a row containing the date that is the last day of the month, containing the allocated payment amount.

Once again, having have opened the Query Editor the first step is to calculate the amount of the monthly payment using a custom column that divides Contract Amount by Months in Contract. This is shown in the Allocated Amount column:

image

Now to generate those monthly payment rows. Since this is reasonably complex I decided to declare a function to do this called EndsOfMonths inside the query, as follows:

= (StartDate, Months) =>
List.Transform(List.Numbers(1, Months), each Date.AddDays(Date.AddMonths(StartDate, _ ), -1))

This function takes the start date for contract and the number of months, and:

  • Uses List.Numbers() to create a list containing numbers from 1 to the number of months in the contract. For example if there were three months in the contract, this would return the list {1,2,3}
  • This list is then passed to List.Transform(), and for each item in the list it does the following:
    • Adds the given number of months to the start date, then
    • Subtracts one day from that date to get the payment date, which will be the last day of the month it is in

Calling this function on each row of the table in a new custom column (called Payment Date here) gives you a list of the payment dates for each contract:

image

All that you need to do then is to click on the Expand icon next to the Payment Date column header and make sure each column has the correct type, and you have your output for loading into the Excel Data Model:

image

Here’s the code for the query:

let

    //Load source data from Excel table

    Source = Excel.CurrentWorkbook(){[Name="Contract"]}[Content],

    //Add custom column for Allocated Amount

    InsertedCustom1 = Table.AddColumn(Source, "Allocated Amount", 

        each [Contract Amount]/[Months In Contract]),

    //Declare function for returning a list of payment dates

    EndsOfMonths = (StartDate, Months) => 

        List.Transform(List.Numbers(1, Months), 

            each Date.AddDays(Date.AddMonths(StartDate, _ ), -1)),

    //Call this function for each contract in a new custom column

    InsertedCustom = Table.AddColumn(InsertedCustom1, "Payment Date", 

        each EndsOfMonths([Contract Start Date], [Months In Contract]) ),

    //Expand the list

    #"Expand Payment Date" = Table.ExpandListColumn(InsertedCustom, "Payment Date"),

    //Set column data types

    ChangedType = Table.TransformColumnTypes(#"Expand Payment Date",

        {{"Contract Start Date", type date}, 

        {"Payment Date", type date}, {"Allocated Amount", type number}, 

        {"Contract Amount", type number}, {"Months In Contract", type number}})

in

    ChangedType

 
There’s one more thing to do though. Since the Contract table contains real dates, it’s a very good idea to have a separate Date table in the Excel Data Model to use with it. I’ve already blogged about how to use a function to generate a Date table in Power Query (as has Matt Masson, whose version adds some extra features) and in that function (called CreateDateTable) can be reused here. Here’s a query that returns a Date table starting at the beginning of the year of the earliest start date in the contract table and ends at the end of the year of the last payment date:
 
let

    //Aggregate the table to find the min contract start date

    //and the max payment date

    GroupedRows = Table.Group(Contract, {}, 

    {{"Min Start Date", each List.Min([Contract Start Date]), type datetime}, 

    {"Max Payment Date", each List.Max([Payment Date]), type datetime}}),

    //Find the first day of the year of the min start date    

    #"Start Date" = DateTime.Date(Date.StartOfYear(GroupedRows{0}[Min Start Date])),

    //Find the last day of the year of the max payment date

    #"End Date" = DateTime.Date(Date.EndOfYear(GroupedRows{0}[Max Payment Date])),

    //Call CreateDateTable with these parameters

    DateTable = CreateDateTable(#"Start Date", #"End Date"),

    //Change data types

    ChangedType = Table.TransformColumnTypes(DateTable,{{"MonthNumberOfYear", type number}

    , {"DayOfWeekNumber", type number}})

in

    ChangedType

 

You can now build a PivotTable to show the payments allocated over the correct ranges:

image

The sample workbook can be downloaded here.

Written by Chris Webb

March 2, 2014 at 9:50 pm

Posted in Uncategorized

2 Responses

Subscribe to comments with RSS.

  1. //Change data types

    Unfortunately, here one always has to define all data types explicitly.
    Otherwise, Day of Month, Year and IsToday are of the type text in Power Pivot.

    The Power BI components do not collaborate very well.

    Frank

    March 3, 2014 at 10:45 am

    • This is something I’m learning about right now, actually – I think it should be possible to avoid having to explicitly in PQ and I don’t think this is a problem with PQ/PP interaction. There’s a special type called ‘any’ which is like a variant, and PQ seems to default to this type when it’s not sure which type should be used. I agree, though, in the scenarios in this post it should do a better job of detecting the types automatically.

      Chris Webb

      March 3, 2014 at 10:49 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers

%d bloggers like this: