Power BI Pricing Announced

I saw today that the prices and licensing options for Power BI have been announced. You can see the details here:
http://www.microsoft.com/en-us/powerBI/pricing.aspx

Thankfully, it’s pretty simple and straightforward.

  • If you already have an Office 365 E3 or E4 subscription, right now you pay $20 per user per month (though that will go up to $33pupm after June 30th this year). This gives you all the cloud-based functionality we’ve seen in the Power BI including Power BI sites, connections to on-prem data sources, mobile BI, data refresh etc.
  • If you already have an Office 2013 Professional Plus licence for your desktop and do not have an Office 365 subscription you can pay $40pupm to get all of the above plus the Sharepoint Online Plan 2 licence you need as a prerequisite for this functionality.
  • If you don’t have an Office 2013 Professional Plus licence either, you can pay $52pupm to get all the Power BI functionality plus Sharepoint Online Plan 2 plus an Office 365 Professional Plus subscription.

Some comments:

  • I’ve heard from Microsoft sources that this works out at about 50% of the cost of Tableau, which is the right price point to aim at in my opinion. As Jen Underwood said in this post, trying to compare the broad range of functionality available in Tableau with Power BI is difficult (though Brad Llewellyn has done a good job looking at specific scenarios); but it’s unavoidable that customers will be comparing Power BI with Tableau and the likes of QlikView. So very good news here.
  • As I’ve said numerous times already, looking at cost of Power BI on its own is misleading because the decision to use it or not will be bound up with larger corporate decisions about migrating to Office 2013 and Office 365.
  • As far as I can see, if you have Excel 2013 standalone, Office 2013 Professional Plus or an equivalent Office 365 SKU and if you do not want to use the cloud functionality, the Excel components (Power Pivot, Power Query, Power View and Power Map) are free to use and do not require a subscription. Some of these Excel addins are also available to users of Office 2010, though not all and for different SKUs of Office 2010. I want to double-check this though. Some functionality of course, such as the ability to share Power Query queries, will only work if you do have a Power BI subscription.
  • While I’m really pleased to see that users with existing desktop installations of Excel are being catered for here, as we’ve seen with the Synonyms functionality it’s clear that if you want the latest functionality in Excel as soon as it’s available you will need to have an Office 365 subscription and a streamed installation. This is the future, although I suspect it may take a long time for corporate IT departments to get round to using the streamed versions.

Aggregating By Local Groups In Power Query

When looking through the Power Query library reference I noticed that the Table.Group() function has a very useful option to aggregate data using what it calls local groups. Rather than explain what this does, let me show you…

Consider the following table showing all of the days in January and whether an employee was on holiday, at work or off sick on any given day in January 2014:

image

Importing this into Power Query and finding the number of days spent on each activity is trivial using the Group By functionality in the Power Query window. Here’s the script that the UI generates:

let

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

    GroupedRows = Table.Group(Source, {"Activity"}, 

        {{"Count of Days", each Table.RowCount(_), type number}})

in

    GroupedRows


And here’s the output it generates:

image

So far so good. But wouldn’t it be useful to know about distinct time ranges spent on each activity? For example, you can see from the first screenshot that this particular employee was off sick from Friday January 17th to Tuesday January 21st, and then again from Friday January 24th to Monday January 27th; you might want to see these aggregated into two separate time ranges. Table.Group() also allows you to do this.

First, here’s a script with an example:

let

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

    FilteredRows = Table.SelectRows(

                    Source

                    , each ([Day Of Week] <> "Saturday" and [Day Of Week] <> "Sunday")),

    TimeRanges = Table.Group(

                    FilteredRows

                    , "Activity"

                    , {

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

                        , {"End Date", each List.Max([Date]), type date}

                        , {"Number of Days", each List.Count([Date]), type number}

                      }

                    , GroupKind.Local)

in

    TimeRanges

Here’s the output:

image

You can see from this screenshot that I now have one row for each consecutive range of days (ignoring weekends) spent either on vacation, working or off sick.

Step-by-step, here’s an explanation of what’s happening in the script:

  • Source imports the data from the table in the worksheet
  • FilteredRows filters out the days that fall on a weekend
  • TimeRanges uses the Table.Group function to do all the interesting stuff:
    • It takes the table returned by the FilteredRows step
    • Does a Group By on the Activity column
    • It calculates the min, max and count of the Date column and adds them as new columns
    • The GroupKind.Local optional parameter is the key to getting the behaviour you can see here. The default type of grouping, GroupKind.Global, does a standard group by across the whole table as seen in the first example above. GroupKind.Local on the other hand aggregates only over consecutive sequences of rows, and this means we see three separate time ranges for the activity “Working” and two separate groups for “Sick”.

Pretty cool, isn’t it?

You can download the sample workbook here.

%d bloggers like this: