Chris Webb's BI Blog

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

Archive for the ‘Power BI’ Category

More Power BI Licensing Details

with 2 comments

At the end of last week I came across an interesting link on Twitter (thanks Devin) with more details on how the ‘new’ Power BI will be licensed, and how existing Power BI subscribers will move to the new experience, that I thought was worth highlighting. Here it is:

Some points to highlight:

  • The Power BI service will become a standalone service and will no longer require SharePoint Online
    This is not exactly a surprise based on what’s been announced so far, but it’s the first time it’s been clearly stated that the ‘new’ Power BI is not dependent on Office 365 and SharePoint Online
  • At general availability of the new experience, existing customers will be asked to move to the new experience
    That’s to say the ‘new’ Power BI is a direct replacement for the old, O365-based Power BI, and that there will only be a single version – the ‘new’ version – of Power BI in the future
  • Power BI will seamlessly work with Office 365 for customer with subscriptions to both
    This move will require configuring the new experience to connect to existing Excel workbooks. Users will need to either point to their Excel workbooks on SharePoint Online or reload these workbooks into Power BI
    If you are an existing Office 365/SharePoint Online/Power BI customer, there will be integration between SharePoint Online and the new Power BI service – so it looks like no functionality is going to be lost in the transition
  • Existing Power BI for Office 365 customers will be able to transition from the existing user experience to the new user experience when it is made available using their existing subscription license…
    This license transition should not interrupt access to the Power BI service.
    So existing customers should be able to move to the ‘new’ Power BI very easily when it is released (remember, we’re currently in preview and the preview of the cloud service is only available to US customers)

If you’re a new customer thinking of signing up for the ‘new’ Power BI, you should check out the licensing options here:

…and also this post on the Power BI support blog on making sure you sign up to the service with the appropriate email address:

Written by Chris Webb

February 9, 2015 at 9:30 am

Posted in Power BI

Bidirectional Relationships And Many-To-Many In The Power BI Designer

with 16 comments

There’s a lot of cool stuff in the new Power BI Designer desktop app, but for me the most important new bit of functionality is one that’s not immediately obvious: relationships between tables in the data model have had a significant upgrade. Let me illustrate…

Bidirectional Relationships

First up: relationships can now filter in two directions. Consider the following two tables in an Excel workbook, a dimension table called Fruit and a fact table called Sales:


When you first load these tables into the Power BI Designer no relationships are created between the tables. To create relationships you need to click on the Manage button on the Home tab so that the Manage Relationships dialog appears. You can then click the Autodetect button and the relationship between the two FruitID columns is created.


However, click on the Edit button and you’ll see something interesting. In the Edit Relationship dialog, under Advanced options, you’ll see that the Cross filter direction is set to Both (the other option is Single).


This means that, not only can you create a report like this with FruitName field on rows axis of a table in a Power View report along with a measure showing the sum of values from the Sales field:


But you can also now take the Date field from the Sales table and put it on rows in the report along with a measure showing the distinct count of values from the Fruit Name field from the Fruit table:


The relationship between the two tables is working in both directions, from the dimension table to the fact table and from the fact table to the dimension table, which is a big change from Power Pivot in Excel where a relationship can only work in one direction (from the dimension table to the fact table). You can still get the original Power Pivot relationship behaviour by setting the Cross filter direction property to Single.


If you thought that was impressive, there’s another implication of this change: many-to-many relationships now work automatically. No nasty DAX is necessary – which is lucky because, at the time of writing, there’s nowhere to use DAX in the Power BI Designer. Here’s the same data as above but with two more tables, so that there is now a classic many-to-many model with a dimension table called Group and a factless fact table called GroupToFruit associating each fruit with one or more groups and each group with one or more fruit.


Here are the relationships in the model, all of which were created using the Autodetect button and all of which have their Cross filter direction set to Both:


And here’s what you see in a report when you put GroupName on rows with a measure showing the sum of Sales:


The sales value for Berries is 35, the sum of the sales for Raspberries and Strawberries; the sales value for Red Fruit is the same because that group contains the same fruit; but the grand total is not the sum of the groups but the total sales for all fruit.


Anyone that has tried to build a reasonably complex model in Power Pivot or SSAS Tabular will understand how big a change this is. Up to now if you wanted to use many-to-many relationships you needed to add extra DAX code to each measure you created, and that added an unwelcome layer of complexity; now it just works. I haven’t thought it through properly yet but I bet that many other modelling scenarios can now be solved with this new functionality too. Time to do some thinking…

Written by Chris Webb

January 1, 2015 at 6:29 pm

Posted in Power BI

Thoughts On The New Power BI

with 24 comments

There’s been a lot of Power BI-related news in the last few days. I think it’s enough to say that there is a new Power BI in town, a very different (and much better) story from what we’ve had up to now. Sadly the preview is only available in the US at the moment.

I’m not going to repeat what the official announcements say, though, and you can read them here if you haven’t seen them already:


Here’s the main Power BI website:

Here’s the documentation for the APIs:

Here’s the YouTube channel with lots of videos:

Here’s the help:


So what does all this mean for us, the Microsoft BI community?

Excel Updates

One of the biggest complaints I’ve heard from people who are just starting to test Power Pivot and Excel 2013 is that it just hasn’t been stable (even compared to Excel 2010 Power Pivot). If you’re evaluated new software then crashes and weird behaviour are a massive turn-off, and I sincerely hope that the new update will improve the situation here and make people’s first impressions of Excel as a BI tool much better.

Power BI Decoupled From Excel And Office 365

OK, so Excel is still important but it’s no longer the centre of the Power BI universe. We can now create models and reports in the browser, and even more significantly we have the Microsoft Power BI Designer, a standalone app which is basically Power Query and Power View (no Power Pivot as yet, but I’m sure that will come) bundled together minus Excel. Also, dashboards and reports no longer have to be stored inside SharePoint, there’s a separate site at for viewing and even creating them.

This is noteworthy for several reasons:

  • The whole Office 2013 Professional Plus/Office 365 requirement is now gone. Yes, Power BI will integrate well with Office 2013 and Office 365 if you have it, but you don’t need them any more. This removes a massive barrier to adoption for Power BI: in the past year or so I’ve seen lots of customers get excited about Power BI and then realise they can’t use it because their organisation isn’t using the right version of Excel, or isn’t an Office 365 customer, or they have the wrong SKU of Office 365, and so on.
  • We no longer have to wait as long for new functionality. The new HTML5 Power View is available in Office 365 and but in Excel on the desktop, even after the latest updates, it’s the old Silverlight Power View still. Tight links to Excel slow down the delivery cycle and prevent Power BI from competing effectively. Excel has clearly not been abandoned but my guess is that it won’t see new features arrive in Excel at the same pace as the rest of Power BI because, well, it’s Excel and it moves very slowly (the Excel team are rightly very cautious about new functionality – if Excel is broken then millions of companies around the world have big problems).
  • There is a down side to this: more choices and more confusion over which tool to use. Do you build dashboards and reports with the Power BI Dashboard Designer and, with all the latest Power View chart types and other cool new stuff; or good old Excel, with its mature, well-understood functionality like PivotTables, cube functions, and the worksheet, plus SharePoint Online and Power BI sites; or a mixture of the two? How well do the options work together? What functionality is available in each option exactly? More conservative users will opt for the latter; BI consultants like me will probably go for the former. But it’s yet another difficult conversation to have with the customer.

Is The New Functionality Any Good?

There are three ways of answering this question: looking at the merits of the new functionality on its own, as it stands now; looking forward to what it will be soon; and comparing it with the competition. The new Power BI is certainly a big improvement over what we’ve had so far, and answers a lot of long-standing requests in terms of functionality. The simple fact that it looks sexy is in itself a great selling point. Here, for example, is what a treemap looks like in a Power View report stored in Office 365:


Here’s a dashboard (though I think ‘dashboard’ is a misleading term here, because it’s not what a lot of us would call a dashboard) from


It’s fast and I am impressed with how easy it is to use too. Although it’s not ready for production use yet (see below) I can imagine it will be quite soon and it should be good enough to win some deals then. It’s still a long way behind some of the competition in some respects, such as variety of chart types, but in other respects (such as the kind of transformations available in Power Query and the types of advanced calculations that are possible in Power Pivot) it’s as good if not better.

Mobile BI On iOS At Last!

At long last we have a mobile BI story on iOS. I haven’t played with it but I have seen demos and it’s pretty good. The touch interface is cool.

This Is Still A Preview

There’s a lot of functionality missing in the current build by the looks of things. The Power BI dashboard designer includes Power Query and Power View, but what about Power Pivot? I’m surprised it’s not included, and I strongly suspect it will be in a later release. Where does Power Map fit in, if at all? We can connect to on-premises SSAS Tabular, but when will be able to connect to Multidimensional? It will be interesting to see what gets released in the next six months. I bet we’ll also see new chart types and data sources very soon. I hope the Power BI team copy the approach the Power Query team have taken and release new builds every month.

And please, please, please Microsoft don’t screw up on the licensing again. No details on it have been released yet. I hope they keep it simple and affordable.

And It’s Still In The Cloud

Apart from the whole Office 2013/365 issue, the other major blocker to adoption of Power BI is the fact that it’s cloud based and that hasn’t changed. Some customers (especially in Europe) are never going to consider a cloud BI tool because of data privacy concerns, and I don’t think that’s going to change soon. Then there are the loyal MS BI customers who have invested heavily in on-premises SharePoint and can’t just ditch all that infrastructure to move to the cloud. Microsoft needs to have a Power BI story for these customers too.


Finally, what I think could be the killer feature for Power BI: the APIs. Microsoft products are most likely to succeed when they give the partner community a platform to build on. Even if Power BI might not match some of the features of other products out there the ability for a partner to push data into Power BI and support obscure data sources will, I think, allow Power BI to beat its rivals in many cases.

In Summary

Overall I think the changes that Microsoft have made to Power BI are the right ones in the circumstances. If I’m honest, over the last year I saw a lot of hype for Power BI, a lot of interest in it from customers, but while Power Pivot and Power Query are being used more and more there are very few customers who are using the whole package. The reasons are clear: the Office 2013 ProPlus/365 requirements; the fact it’s in the cloud; and the fact that it’s horrendously difficult to understand what Power BI even is and how the components fit together. The new Power BI deals with some of those problems but not all; I hope Microsoft has done enough to ensure that Power BI gets traction in the marketplace. The new functionality is really good and I’m a lot more optimistic about the future than I was. If Microsoft can keep up the momentum it will have a hit on its hands.

Written by Chris Webb

December 19, 2014 at 9:39 am

Posted in Power BI

New Power Query Videos Live

leave a comment »

I have recorded two new videos for Project Botticelli about Power Query that are now available to view. They are:

What Is Power Query?

This is short introduction to Power Query is free to view (registration required) and covers just the basics.



Power Query Fundamentals

This 45 minute video goes into more detail about all of the cool things you can do in the Power Query Query Editor. You’re going to need a subscription to see this, but if you subscribe you will of course get access to loads of other videos too (including my MDX course, Marco and Alberto’s DAX videos, and lots of other cool MS BI content).



Don’t forget that my Power Query book is still available (even if it’s now a little out of date because the Power Query UI has changed over the last few months, you’ll probably want it for the more advanced content which is still good) and that I have several classroom-based training courses on Power BI and SSAS in London next year.

Written by Chris Webb

December 1, 2014 at 3:00 pm

News On SSAS Data Source Support In Power BI

with 14 comments

Yesterday we heard (again) that SSAS will be supported as a data source for cloud-based reports in Power BI. Today, in a session, two new important details on this emerged:

  • It will work with both Tabular and Multidimensional
  • It will connect as the user running the query, so SSAS security (eg dimension security) will work just the same as it does on-premises. No special setup will be needed; there were no details apart from the fact it will work using the EffectiveUserName connection string property.

I’m sure a lot of people will be interested to hear this…

Written by Chris Webb

November 6, 2014 at 10:41 pm

Thoughts On Office Sway And BI

with 3 comments

When I first saw the announcement about Office Sway last week, I thought – well, you can probably guess what I thought. Does it have any potential for BI? After all, the Sway team are clearly targeting business users (as well as hipster designers and schoolchildren): look at the Northwest Aquarium and Smith Fashion Expansion samples, and notice that they contain tables, charts and infographics. What’s more, data storytelling is currently a very hot concept and Sway is clearly all about telling stories. Wouldn’t it be cool if you could have interactive PivotTables, PivotCharts and Power View reports from your Power BI site embedded in a Sway? It would be a much more engaging way of presenting data than yet another PowerPoint deck.

I have no idea whether any integration between Sway and Power BI is actually planned (I have learned not to get my hopes up about this type of thing), but even if it isn’t maybe someone at Microsoft will read this post and think about the possibilities… And isn’t this kind of collaboration between different teams supposedly one of the advantages Microsoft has over its competitors in the BI space?

Office Sway introductory video


PS I want a pink octopus costume just like the one that girl in the video has

Written by Chris Webb

October 9, 2014 at 4:38 pm

Creating Histograms With Power Query

with 5 comments

A few months ago someone at a conference asked me what the Power Query Table.Partition() function could be used for, and I had to admit I had no idea. However, when I thought about it, I realised one obvious use: for creating histograms! Now I know there are lots of other good ways to create histograms in Excel but here’s one more, and hopefully it will satisfy the curiosity of anyone else who is wondering about Table.Partition().

Let’s start with a table in Excel (called “Customers”) containing a list of names and ages:


Here’s the M code for the query to find the buckets:


    //Get data from Customers table

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

    //Get a list of all the values in the Age column

    Ages = Table.Column(Source,"Age"),

    //Find the maximum age

    MaxAge = List.Max(Ages),

    //The number of buckets is the max age divided by ten, then rounded up to the nearest integer

    NumberOfBuckets = Number.RoundUp(MaxAge/10),

    //Hash function to determine which bucket each customer goes into

    BucketHashFunction = (age) => Number.RoundDown(age/10),

    //Use Table.Partition() to split the table into multiple buckets

    CreateBuckets = Table.Partition(Source, "Age", NumberOfBuckets, BucketHashFunction),

    //Turn the resulting list into a table

    #"Table from List" = Table.FromList(CreateBuckets, Splitter.SplitByNothing()

                           , null, null, ExtraValues.Error),

    //Add a zero-based index column

    #"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 0, 1),

    //Calculate the name of each bucket

    #"Added Custom" = Table.AddColumn(#"Added Index", "Bucket", 

                        each Number.ToText([Index]*10) & " to " & Number.ToText(([Index]+1)*10)),

    //Find the number of rows in each bucket - ie the count of customers

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Count", each Table.RowCount([Column1])),

    //Remove unnecessary columns

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1", "Index"})


    #"Removed Columns"


And here’s the output in Excel, with a bar chart:


How does this work?

  • After loading the data from the Excel table in the Source step, the first problem is to determine how many buckets we’ll need. This is fairly straightforward: I use Table.Column() to get a list containing all of the values in the Age column, then use List.Max() to find the maximum age, then divide this number by ten and round up to the nearest integer.
  • Now for Table.Partition(). The first thing to understand about this function is what it returns: it takes a table and returns a list of tables, so you start with one table and end up with multiple tables. Each row from the original table will end up in one of the output tables. A list object is something like an array.
  • One of the parameters that the Table.Partition() function needs is a hash function that determines which bucket table each row from the original table goes into. The BucketHashFunction step serves this purpose here: it takes a value, divides it by ten and rounds the result down; for example pass in the age 88 and you get the value 8 back.
  • The CreateBuckets step calls Table.Partition() with the four parameters it needs: the name of the table to partition, the column to partition by, the number of buckets to create and the hash function. For each row in the original table the age of each customer is passed to the hash function. The number that the hash function returns is the index of the table in the list that Table.Partition() returns. In the example above nine buckets are created, so Table.Partition() returns a list containing nine tables; for the age 8, the hash function returns 0 so the row is put in the table at index 0 in the list; for the age 88 the hash function returns 8, so the row is put in the table at index 8 in the list. The output of this step, the list of tables, looks like this:

  • The next thing to do is to convert the list itself to a table, then add a custom column to show the names for each bucket. This is achieved by adding a zero-based index column and then using that index value to generate the required text in the step #”Added Custom”.
  • Next, find the number of customers in each bucket. Remember that at this point the query still includes a column (called “Column1”) that contains a value of type table, so all that is needed is to create another custom column that calls Table.RowCount() for each bucket table, as seen in the step #”Added Custom1”.
  • Finally I remove the columns that aren’t needed for the output table.

I’m not convinced this is the most efficient solution for large data sets (I bet query folding stops very early on if you try this on a SQL Server data source) but it’s a good example of how Table.Partition() works. What other uses for it can you think of?

You can download the sample workbook here.

Written by Chris Webb

October 7, 2014 at 9:42 pm

Posted in Power BI, Power Query


Get every new post delivered to your Inbox.

Join 3,715 other followers