Chris Webb's BI Blog

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

Allocation in Power Query

with 12 comments

Now that the brave new world of self-service BI is upon us, old-school corporate BI types like me need to sharpen our Excel skills – and anyone learning Excel will, sooner or later, end up on Bill Jelen (aka Mr Excel)’s site. I found his latest podcast on splitting the value of a contract over N months particularly interesting not only because I had to deal with a similar problem with a client only a few weeks ago but also because the problem of allocation in Power Query is something I’ve been meaning to blog about for a while. In this post I’m going to take the same data that Bill and Mike Girvin (whose book on Excel array formulas I got for Christmas!) used in the podcast and show how to achieve the same results they did but in Power Query and Power Pivot.

My starting point is an Excel sheet with two tables named Contract and Month, shown below:

image

It’s not exactly the same layout as in the podcast but that’s deliberate – I want to keep my source data and my output (which could be a PivotTable, cube formulas or a Power View sheet) separate.

Next, I import the Month table into Power Query using the From Table button and then click on the Add Index button to add an index column, so that the query output is as follows:

image

I don’t need to load this anywhere though, even though I’m going to use its output in the next query, so I leave both of the boxes in the Load Settings section of the Query Editor unchecked and go back to the worksheet:

image

Next, I import the Contract table and add an index column in the same way:

image

I can now add a custom column to calculate the monthly amount by dividing Contract Amount by Months In Contract:

image

Now comes the interesting bit. I insert another custom column and this time the M expression to paste into the dialog is:

Table.FirstN(Month, [Months In Contract])

In each row this column contains a table containing the first N rows of the Month table, where N is the value from the [Months In Contract] column. The output is this:

image

I then just need to click on the expand icon next to the column header of the Custom column to repeat each contract row for all the months it applies to, rename the columns and set the column types appropriately, and I’m ready to load into the Excel Data Model:

image

Here’s the full M code for both queries:

--Month Query

let

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

    InsertedIndex = Table.AddIndexColumn(Source,"Index"),

    ReorderedColumns = Table.ReorderColumns(InsertedIndex,{"Index", "Month"})

in

    ReorderedColumns


--Contract Query

let

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

    InsertedIndex = Table.AddIndexColumn(Source,"Index"),

    RenamedColumns = Table.RenameColumns(InsertedIndex,{{"Index", "ContractID"}}),

    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"ContractID",

                            "Months In Contract", "Contract Amount"}),

    InsertedCustom = Table.AddColumn(ReorderedColumns, "Allocated Amount",

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

    InsertedCustom1 = Table.AddColumn(InsertedCustom, "Custom",

                            each Table.FirstN(Month, [Months In Contract])),

    #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom1, "Custom",

                            {"Index", "Month"}, {"Custom.Index", "Custom.Month"}),

    RenamedColumns1 = Table.RenameColumns(#"Expand Custom",{{"Custom.Index", "MonthID"},

                            {"Custom.Month", "Month"}}),

    ChangedType = Table.TransformColumnTypes(RenamedColumns1,{{"Allocated Amount", type number},

                            {"Contract Amount", type number}, {"MonthID", type number},

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

in

    ChangedType

 

Last of all, I need to go into the Power Pivot window and do two things:

  • Use the Sort By Column functionality to sort my Month column by MonthID
  • Format my Allocated Amount column using a dollar sign

And I’m ready! I can now create a PivotTable containing my allocated values:

image

Maybe it’s a little bit more long-winded than Bill or Mike’s examples but I don’t think it’s any more complex. And of course, now the data is in the Excel Data Model I have a lot more flexibility on how to present the data. For example I can use Power View with no remodelling or formula changes necessary:

image

One last point: I know it’s good practise to use a separate Date table with Power Pivot. I didn’t do so here because I wanted to keep as close to the original example as possible. And because I’m lazy.

You can download the demo workbook here.

Written by Chris Webb

February 24, 2014 at 9:10 pm

Posted in Power Query

12 Responses

Subscribe to comments with RSS.

  1. Hi Chris,

    Like Mr Excel I think your article would make amazing Podcast,
    any plan on producing videos on your blog and then you can have som additionnal detail such as the files etc on the blog article …

    Anyway keep up the good and fine works from yours.

    As usual you’re crushing it ;-)

    Kind Regards,

    // Tarek

    Tarek Demiati (@TarekDemiati)

    February 24, 2014 at 9:37 pm

    • It did occur to me that this would make a good podcast… but I’m on the road this week, and I don’t have my headset with me unfortunately.

      Chris Webb

      February 24, 2014 at 9:45 pm

  2. Chris – that is an amazing set of steps! Every time I see a new Power Query technique, it amazes me of how much it can do. (Please finish your Power Query book soon – I have to get a copy.)

    Bill Jelen

    February 25, 2014 at 4:22 am

  3. Chris,

    This is a neat example, and does show some of Power Query’s capabilities, and it is always great to see Power Query postings. But I wonder if this is not a case of ‘old-school corporate BI types’ over-engineering it :-).

    You say that it’s more long-winded but not more complex. I have to disagree, I found it quite a bit more complex, and as I followed your post I had a few issues.
    When I inserted the column for the Allocated Amount and then renamed the column, a two-step process, this gave me errors in later steps. I found that I had to I had to delete the RenameColumn statement, and then change the column name (which was of course Custom) in the InsertColumn command in the formula bar.
    When I was adding the custom column to get the Month data, it inserted an extra AddColumn statement, ‘each Table.AddColumn(InsertedCustom, “Custom”,’. When I tried to expand the column, I could see the Contract columns not the Month columns. Removing that extra statement and the final closing brackets solved the problem, but it took a moment for me to figure out what was going on.
    In addition, I couldn’t see why you added an index to both tables, so I tried it without (I guess that maybe you always add an index column as a best practices approach). Everything worked fine, except of course I couldn’t sort by month name in PowerPivot, but I can do that in the pivot table just as easily.

    To me, the standard Excel solution of determining the allocations that Bill shows is far simpler and far more intuitive, more likely to be understood by a self-service BI consumer. Obviously the issue is that you then have a cross-tab report that is not conducive to further analysis, but that is easily resolved. Take that extended Contracts table into Power Query, unpivot the allocation columns, filter out the blank rows, and you can then load that into your datamodel and do all the good things with it. To me that is very simple, no extra month names table, very straight-forward, something a good Excel pro could easily follow.

    wessexbob

    February 25, 2014 at 12:37 pm

    • I guess which approach you prefer depends on how good your Excel skills are to begin with! You’re right, though, most real users already have better Excel skills than PQ skills. I’m not sure why you had problems renaming columns though – PQ is usually very good at handling this kind of change – or why you got an extra AddColumn statement (did you click the button twice?). I added index columns because I like primary keys on tables: in this case it made no difference, but if two contracts had the same amounts then that would make later reporting more difficult.

      Chris Webb

      February 25, 2014 at 12:51 pm

      • I’ve just thought – did you paste the whole expression
        = Table.AddColumn(InsertedCustom, “Custom”, each Table.FirstN(Month, [Months In Contract]))
        into the dialog box that appears when you click the Insert Custom Column button? If so, then I’m sorry, I should have been clearer – the above is the code that gets generated for the step. All you need to paste into the dialog is
        Table.FirstN(Month, [Months In Contract])

        Chris Webb

        February 25, 2014 at 12:56 pm

      • I am not sure about that renaming, I don’t recall having a problem before, but then I didn’t use the renamed columns like this before. On the indexes, once I had realised that the index columns were not necessary, I guessed that was why you did it. I thought it was useful to point it out for anyone who was trying to follow what you did rather than just do it by rote.

        On the other problem, yes that was exactly what I did initially. I figured it out, but that is part of what I mean by complexity. I am not at your level on M and I would guess that few people are (perhaps we all will be when we get your book :-)), and so when the problem manifested itself, it is far from obvious what happened and how to resolve it.

        wessexbob

        February 25, 2014 at 1:12 pm

      • I’ve edited the post to make the part about the custom column clearer.

        Chris Webb

        February 25, 2014 at 1:21 pm

  4. Using Table.FirstN for this is a brilliant idea.

    To stick to the original approach, one can use Table.Pivot (not in the UI, I’m afraid). BTW, indexing the tables is not necessary.

    let
    Source = Excel.CurrentWorkbook(){[Name="Contract"]}[Content],
    ListOfMonths = Table.ToList(Excel.CurrentWorkbook(){[Name="Month"]}[Content]),
    AllocatedAmount = Table.AddColumn(Source, “Allocated Amount”, each [Contract Amount]/[Months In Contract]),
    FirstNMonth = Table.AddColumn(AllocatedAmount, “Custom”, each Table.FirstN(Month, [Months In Contract])),
    ExpandedTableColumn = Table.ExpandTableColumn(FirstNMonth, “Custom”, {“Month”}, {“Month”}),
    PivotTable = Table.Pivot(ExpandedTableColumn, ListOfMonths, “Month”, “Allocated Amount”)
    in
    PivotTable

    Frank

    February 26, 2014 at 2:17 pm

    • Thanks Frank. I know the indexing isn’t strictly necessary, but for the months it allows me to use Sort By Column so I don’t need to do sorting in the PivotTable (and it’s the only way to get months sorted properly in Power View); for the contracts it means I have a primary key on the table so if there are two contacts with the same amount and number of months they won’t get aggregated by accident in the output.

      Chris Webb

      February 26, 2014 at 2:33 pm

  5. […] week’s post on allocation in Power Query caused quite a lot of interest, so I thought I would follow it up with […]

  6. […] week’s post on allocation in Power Query caused quite a lot of interest, so I thought I would follow it up with […]


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,311 other followers

%d bloggers like this: