Chris Webb's BI Blog

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

Archive for the ‘Power BI’ Category

Web Services And POST Requests In Power Query

with 5 comments

In my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function generates a GET request by default, you can make it generate a POST request by specifying the Content option. Since this is a useful thing to be able to do I thought I’d put together a detailed example of how this works.

For my example I’m going to use the Bing Maps Elevations API, which allows you to get the elevation in metres for a set of geographic locations. You can read the documentation here:

As it says at the bottom of the page, if you have a large number of locations to pass to the web service you can do so by passing them as a comma delimited list of latitudes and longitudes using a POST request. Here’s the code for a Power Query query that generates a list of latitudes and longitudes that stretches across the UK from North Wales in the west to the Wash in the east and finds the elevation for each point:


    //Insert your Bing Maps API key here

    BingMapsKey = "xxxx",


    Latitude = 53,

    //Generate a list of longitudes

    Longitudes = List.Numbers(-4.5,66,0.1),

    //Generate a list of latitudes and longitudes

    PointList = List.Transform(Longitudes, 

        each  Number.ToText(Latitude) & "," & Number.ToText(_)),

    //Turn this list to comma delimited text

    PointListText = Text.Combine(PointList,","),

    //Add the text "points=" to the beginning

    PostContents = "points=" & PointListText,

    //Call the Elevations web service

    GetElevations = Web.Contents(

                    "" & BingMapsKey,


    //Treat the result as a JSON document

    ImportedJSON = Json.Document(GetElevations),

    //Navigate to the elevations data

    resourceSets = ImportedJSON[resourceSets],

    resourceSets1 = resourceSets{0},

    resources = resourceSets1[resources],

    resources1 = resources{0},

    elevations = resources1[elevations],

    //Turn the elevations data into a table

    TableFromList = Table.FromList(elevations, Splitter.SplitByNothing(), 

                                null, null, ExtraValues.Error),

    //Rename the column containing the elevations

    RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Elevations"}}),

    //Add a column containing the latitude

    InsertedCustom = Table.AddColumn(RenamedColumns, "Latitude", each Latitude),

    //Add an index column

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

    //Use the index column to find the longitude for the current row

    InsertedCustom1 = Table.AddColumn(InsertedIndex, "Longitude", each Longitudes{[Index]}),

    //Remove the index column

    RemovedColumns = Table.RemoveColumns(InsertedCustom1,{"Index"}),

    //Set all columns to data type number

    ChangedType = Table.TransformColumnTypes(RemovedColumns,

                    {{"Elevations", type number}, {"Latitude", type number}, 

                    {"Longitude", type number}})




It’s quite easy to edit the code so that it generates a list of latitudes and longitudes across the country of your choice…

Two things to point out:

  • To get this to work you need to insert a Bing Maps API key in the first step where indicated. If you don’t have one, you can get your own at
  • The important step is GetElevations. The code is:


    "" & BingMapsKey,


    You can see here how the list of locations is passed to the Web.Contents() function (documentation here) via the Content field; notice also that I’ve had to use Text.ToBinary() on the text that I’m passing in.

Here’s the output in Power Map:


You can download the sample workbook here.

Written by Chris Webb

April 19, 2014 at 9:00 am

Analysing Voting In the Power BI Competition – With Power BI!

with 8 comments

As I mentioned the other day, the public voting round of the Power BI competition has now started and the entrants are now competing for a place in the top ten and a whole bunch of cool prizes including XBoxes and Surface Pros. Having entered a demo myself I’m naturally very keen to see how I’m doing, but unfortunately doing this is a bit of a pain – you have to go to, click on the Entries tab, sort them, find your entry, and so on… So I thought to myself, isn’t there a better way? And of course there is… using Power BI!

Power Query is of course the tool to use to scrape the voting data from the Power BI site. It wasn’t straightforward to do but on the other hand it wasn’t impossible: there’s no single table of results, and indeed the results are spread over four different pages. To load the data into the Excel Data Model I:

  • Created a Power Query function to scrape the data from the first page
    • First I created a query to scrape the data from the first page using the “From Web” button
    • I then did a *lot* of searching around in the HTML to get the data (for this reason I’m not going to paste the code of the function here because it’s ugly)
    • I then deleted a lot of columns, created some new custom columns, and pivoted the data until in was in a nice tabular form
    • Finally, once I was sure the query was working properly I turned it into a function that could get data from any page
  • Next I created a table with five rows in it, called the function five times, once for each row, to get the data from each page
  • Then I merged all the data into a single table of results
  • I created another function to calculate the rank of an entry (which itself was an interesting challenge) and added a rank column to the merged table
  • Last of all, I created some Power View sheets to analyse the data

You can download the entire workbook here. I’ll warn you, the code isn’t pretty but it does the job and it’s got a few interesting features in for all you Power Query fans.

Now let’s have a look at what the data shows.

At the time of writing this I’m currently in 16th place – and not in line for an XBox alas. You can of course change this by


and that’s even if you’ve voted for me already – you can vote once every 24 hours! But I digress…

The obvious first thing to do was to create a leader board sorted by rank:


As you can see, at the moment Carlos Costa is way out in front with a massive 180 votes; after that there’s a group of people fighting for a top five finish; and after that there’s a large group of people who might just squeeze into the lower reaches of the top ten.

Here’s a column chart showing this more clearly:


What are the top-ranked guys doing to get so many votes? Is it something to do with the number of people seeing their entry? If you put votes and views next to each other, it’s not easy to say what’s going on:


I’ve got the second largest number of views, but I’m in the middle of the pack. The problem for me is that I was one of the first people to enter and most of my views were well before voting opened; unfortunately there’s no way of knowing how many views each entry had after voting opened so I can’t say for sure what’s going on here. Certainly Alexander Pinkus submitted his demo “Few Facts About Dinosaurs” around the same time as me, has around the same number of views, and he’s currently ranked number 5. From that I can only deduce that his demo is better than mine (and having watched it I can say it is very good); he certainly has more exciting subject matter. Who doesn’t love dinosaurs? There are several other demos that have got particularly eye-catching subject matter and/or titles and they’re also doing well.

Here are views and votes plotted on a scatter chart, with the top 10, 20, 30 and so on shown in different colours:


I think this makes it a little easier to disregard outliers like me, and it’s probably fair to say that there is some kind of link between views and votes, at least for those in the top 20.

Now here’s one last column graph, showing the average number of views per vote for the top 20 ranked entrants:


You can see how badly I’m doing in this respect, despite my begging and pleading both here and on Twitter; conversely you can see that Mike Tetreault at the other end is obviously very good at getting his vote out.

I would like to say at this point that it’s tempting to moan and complain that people have ‘cheated’ and got their friends, family and colleagues to vote for them. To be honest, I think that everyone who’s got any significant number of votes will have ‘cheated’ like this to some extent. I certainly have – I’ve used my blog and other social media to try to get as many votes as possible. Indeed this post itself is a ruse to try to get more votes! At the end of the day the whole point of this competition is for Microsoft to get as many people as possible to see Power BI, so more people want to buy it. Therefore this mad scramble by entrants to get as many votes as possible will benefit all of us in the end.

Anyway, by the time you read this post there’s a strong chance that the patterns here will have changed completely. It’s a shame there isn’t more data available to play with – it would be great to have the time and date of each vote cast, and even the location of the person casting the vote. Given that you have to have a Facebook account to vote I suspect that someone somewhere does have all of this data, and more… so I wonder if they’re using Power BI too?

Written by Chris Webb

January 22, 2014 at 10:18 pm

Posted in Power BI

My Power BI Competition Entry

with 8 comments

So the Power BI competition has entered the shameless self-promotion public voting round and I would like to direction your attention to my entry, which can be found here. Please follow the link and vote for me! Your support is much appreciated.

In the video I take a look at road traffic accident open data released by the UK government and answer questions such as:

  • Are British roads getting safer or more dangerous?
  • Which days of the week do most accidents occur on?
  • Which age groups are most likely to be involved in accidents?
  • Which is the most dangerous section of the M4 motorway? (Spoiler: avoid Port Talbot when visiting South Wales)

The video itself is more thrill-packed than Star Wars, more epic than the Lord of the Rings trilogy and more intellectual than any film Ingmar Bergman ever made. It is guaranteed to win several Oscars and recommended viewing for the whole family.

Here are some screenshots to whet your appetite:




There are plenty of other very impressive entries that deserve to be seen, so head over to to take a look. 

Incidentally, if you’re planning to attend the PASS BA Conference this year you can get a $150 discount by using the discount code:

And if you can’t make it to the conference but do want to learn more about Microsoft’s self-service BI stack, check out my Power BI and Power Pivot courses in London this March.

Written by Chris Webb

January 20, 2014 at 7:30 pm

Posted in Power BI

“My Power BI” Sites

leave a comment »

One feature that was released in the December update for Power BI but got lost in all the fuss over Q&A was the ability to favourite a report and have it appear in your own “My Power BI” site. In fact there are several interesting things to see here, and while they are documented I thought it would be useful to show some screenshots.

On the main Power BI page, you can now click on the ellipses at the bottom right hand corner of a report to favourite it.


This means it will appear in your own “My Power BI” site. I hadn’t really noticed this page before; you can find a link to it in the top right hand corner of the screenshot above.

With no reports favourited your “My Power BI” site looks like this:


With a report favourited it looks like this:


So, it’s a nice place to find a user’s top reports. There’s more though. Clicking on the ‘data’ link shows you a dashboard with your own usage stats on:


Plus a list of all the Power Query queries you have shared:


And finally metadata for all of the OData data sources (this includes data sources made available through the Data Management Gateway as well as public data searches) you have used in Power Query on the desktop, even if you have never saved the workbook they’re used in to Power BI:


You can find more information on what’s going on in this last screenshot here. I quote:

When users either connect to various external and internal data sources or share queries using Power Query, metadata for the connected data sources or the underlying data sources for the shared queries respectively gets created in the cloud-based metadata repository of Power BI for Office 365. The metadata of such data sources become available in the Manage Data portal.

The Manage Data portal displays all the data sources available in the cloud-based metadata repository of Power BI whether or not you have access to the data in the data source. The data source listing displays the name, location, description, the user who last modified it, and the date when it was last modified.

I have to admit that this was a bit of a surprise to see, but I probably read something about this and forgot about it. I can understand why it’s necessary: Power BI is doing this is to help data stewards manage requests for permissions to access these data sources. Here’s the metadata that gets stored:


So, lots of interesting stuff there. I have to say that SharePoint Online and Power BI are a bit of a rabbit warren and some of the functionality in there is very well hidden, however good the docs are…

Written by Chris Webb

January 14, 2014 at 1:50 pm

Posted in Power BI

Power BI Pricing Announced

with 11 comments

I saw today that the prices and licensing options for Power BI have been announced. You can see the details here:

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.

Written by Chris Webb

January 4, 2014 at 3:13 pm

Posted in Power BI

Power BI Review, Part 2.5: Q&A (Now I’ve Used It With My Data)

with 6 comments

A few months ago I posted a review of Q&A, the natural language query functionality in Power BI, based on the sample data sets that were then available. Last week, finally, we got the news that we could enable Q&A on our own Power Pivot models, and having played with this new release I thought it was a good idea to post an update to my original thoughts.

The first thing to point out is that even if you’ve got a Power BI Preview tenant you will need the latest version of Power Pivot for Excel to be able to get the best out of Q&A. This latest release contains some new functionality to add ‘Synonyms’ to the model – what this means is that it allows you, as a model creator, to tell Power BI about other names that end users might use when querying your model. For example on a Geography dimension you might have a column called State but if you are a multinational company you may find that while your State column contains the names of states in the USA, it might contain the names of cantons in Switzerland, counties in the UK, departments in France and so on. As a result you will want Power BI to know that if a user asks for sales by county in the UK that it should actually look in the State column. Devin Knight has already written a good post showing how synonyms work with Q&A which you can see here.

Another complication is that, at the time of writing, the Synonym functionality is only available to users who have installed the streamed version of Office 2013 from Office 365. I have an Office 365 subscription but I had installed Office from an msi before that, so I had to uninstall Office and reinstall the streamed version to be able to see Synonyms – I assume that support for Synonyms in the non-streamed version of Excel will come at some point soon in the future, but in general I would expect that new Power BI functionality will appear first in the streamed version of Office first so if you’re serious about BI you should change over to it as soon as you can. Melissa Coates has a lot more detail on this issue here.

But enough about setup, what about Q&A? The data that I tested it on was a model I’ve been using for user group and conference demos for about six months now, which contains data from the UK’s Land Registry and details all residential property transactions in England and Wales in 2013. It’s fairly simple – two tables, a date table and a transactions table containing around 0.5 million rows – so probably a lot simpler than the average Power Pivot model, but nonetheless real data and one which had been polished for demo purposes. The Excel file holding it is around 25MB so I was well within the Power BI file size limits.

My initial impression after I had added my existing model (with no synonyms etc) to Q&A was that while it worked reasonably well, it worked nowhere near as well as the demo models I had seen. I then set about making changes to the model and re-uploading it, and these changes made all the difference. Some examples of the things I did are:

  • Changed table and column names. In my model I had already taken the trouble to make them human readable, but this did not necessarily mean they were suitable for Q&A. For example, my main fact table was called ‘Land Registry’, so at first Q&A kept suggesting questions like “How many land registries were there in June…” which clearly makes no sense. Renaming the fact table to ‘Sales’ fixed this.
  • Setting synonyms. Unsurprisingly, this had a big impact on usability in the same way that changing the table and column names did. I found that I had to go through several iterations of uploading the data, writing questions, seeing what worked and what didn’t, and adding more synonyms before I had a set that I was happy with; I can imagine that in the real world you’d need to round up several end users and lock them in a room to see how they phrased their questions so as to get a really good list of synonyms for them.
  • Setting Power View-related properties. This included setting the Default Field Set on a table, so I only saw a few important fields in a meaningful order when Q&A returned a table result; and also Summarize By so that Q&A didn’t try to aggregate year values. All of this makes sense given how closely-related Q&A and Power View are, but even though I had a reasonably ‘finished’ model to start off with I still hadn’t set all of these properties because I knew I was never going to try to sum up a year column.
  • Adding new columns. There were a number of cases where I realised that I, as a human user, was able to make assumptions about the data that Q&A could not. For example the source data records sales of four different types of residential property: terraced, detached, semi-detached and flat. The first three are types of house, but the source data doesn’t actually state that they are types houses anywhere so in order to see the total number of sales of houses I had to add another column to explicitly define which property types were houses.
  • Disambiguation. Probably the most irritating thing about the Bing geocoding service that Power View and Q&A use is the way it always chooses a US location when you give it an ambiguous place name. Therefore when looking at sales by town I would see the town name “Bristol” show up on the map as Bristol, Tennessee (population 24,821) rather than Bristol in England (population 416,400). Creating a new column with town name and country concatenated stopped this happening.

The Microsoft blog post I referenced above announcing Q&A promises that a more detailed guide to configuring models for Q&A will be published soon, which is good news. The important point to take away from this, though, is that even the most polished Power Pivot models will need additional tweaks and improvements in order to get the best out of Q&A.

The big question remains, though, whether Q&A will be something that end users actually get some value from. As a not-very-scientific test of this I handed my laptop over to my wife (who has no experience of BI tools but who has a healthy interest in property prices) to see how easy it was for her to use, and straight away she was able to write queries and find the information she was looking for, more or less. There were a still few cases where Q&A and/or my model failed, such as when she searched for “average house price in Amersham” – the model has a measure for “average price”, it knows about the property type “house” and the town “Amersham”, but “average house price” confused it and the query had to be rewritten as “average price of a house in Amersham”. Overall, though, I was pleasantly surprised and as a result I’m rather less sceptical than I was about Q&A’s usefulness, even if I’m still not 100% convinced yet.

Written by Chris Webb

December 23, 2013 at 12:45 am

Posted in Cloud, Excel, Power BI, Q&A

Generating A Date Dimension Table In Power Query

with 28 comments

There are hundreds of different methods for generating a Date dimension table for use in Power Pivot: you can use TSQL, Excel formulas, import from the DateStream table in the Windows Azure Marketplace, and there’s even an app for it. Nevertheless I thought it would be a good idea to solve this problem in Power Query because:

  • It seems like the natural tool to use, and a date table is one of the first tables a data steward will want to share with his/her users.
  • It’s a very good way to learn about and demonstrate all the date functionality in M and Power Query. The more M code examples that there are out there, the better, right?
  • Having just signed the contract to write a whole book on Power Query I need to master M very quickly!

Here’s how I did it. First of all, I decided to implement a function to create the table rather than a regular query; this function takes two parameters: the start date and the end date of the range of dates to appear in the table. Here’s the function I came up with:


    CreateDateTable = (StartDate, EndDate) =>




    //Create lists of month and day names for use later on

    MonthList = {"January", "February", "March", "April", "May", "June"

                 , "July", "August", "September", "October", "November", "December"},

    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

    //Find the number of days between the end date and the start date

    NumberOfDates = Duration.Days(EndDate-StartDate),

    //Generate a continuous list of dates from the start date to the end date

    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

    //Turn this list into a table

    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}

                     , null, ExtraValues.Error),

    //Caste the single column in the table to type date

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

    //Add custom columns for day of month, month number, year

    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),

    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),

    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),

    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),

    //Since Power Query doesn't have functions to return day or month names, 

    //use the lists created earlier for this

    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),

    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),

    //Add a column that returns true if the date on rows is the current date

    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))






Some things to point out:

  • Look – comments! Both multi-line /* */ and single line //. Adding comments to complex Power Query queries and functions is of course a Good Thing and something we should all be doing.
  • The way I created the table from the start and end date is quite interesting: the List.Dates() function allows you to generate a list of dates from one date to another; I was then able to take this list and turn it into a table with Table.FromList().
  • Adding extra columns to the table for the year and month number and so on is fairly straightforward. However, as far as I can see, Power Query does not have the ability to return month or day names (as opposed to numbers) from a date, so to get round this I created two lists called MonthList and DayList containing the months of the year and the days of the week, in order, took the day and month numbers and used these to find the name at the nth position in the list (see the MonthName and DayName steps).
  • Date.IsInCurrentDay() compares a date with the current system date and allows you to see if a date is today’s date. There are a whole bunch of other interesting functions like this such as Date.IsInPreviousWeek(), Date.IsInCurrentYear() and so on, useful for creating relative date dimension tables like the one I describe here.

With the function created it’s very easy to generate a table using it. For example, I created a parameter table with start date and end date columns like so:


I then used this table as the starting point for a new query that called my function:


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

    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}

                  , {"End Date", type date}}),

    CallCreateDateTable = CreateDateTable(ChangedType[Start Date]{0}

                          ,ChangedType[End Date]{0})



Here’s the output:


You can download my demo workbook with all the code in from here.

PS If you haven’t downloaded the November update for Power Query already, I recommend you do so – the UI has been improved a lot.

Written by Chris Webb

November 19, 2013 at 5:32 pm

Posted in Power BI, Power Query

Power BI Preview Review Part 2: Q&A

with 20 comments

Today the natural language query functionality in Power BI, Q&A, was enabled in my Power BI Preview tenant. This is the last major piece of functionality to be added to the Preview, and of course as soon as I heard that it was there I had to go and play with it, despite being on a slow mobile WIFI connection and having tons of emails to catch up with. So apologies to anyone who is waiting for an email from me!

First thing to point out: you can only use it on some sample workbooks provided by Microsoft, and these get added to your Power BI site when you enable Q&A. The ability to use it on your own data is coming soon but I don’t think this stopped me from getting a feel for how well it works.

With Q&A switched on my Power BI site now looks like this:


Notice that the top half of the page now has a ‘Popular Questions’ area which takes you to some pre-defined questions on the sample data, while in the bottom half you see the sample workbooks along with any other workbooks you have uploaded. The two sample workbooks contain data on medals won at the Olympics and sales of drinks in a bar.

Here’s what one of the sample questions looks like when you click on it:


At the top of the screen you have a search box where you can type your question; immediately underneath that box is how Q&A has interpreted the question. On the left-hand side you see the workbook used as the data source, and you can see alternative visualisations for the data that you can choose. On the right-hand side there are some suggested questions for this data and other hints and tips. It’s all pretty straightforward.

But enough of the descriptions – there are two big questions that need to be answered here:

  1. Does it work? That’s to say, will a real user be able to ask questions and find they data they want?
  2. Will anyone use it, or is it just demo-ware?

I’m aware these are loaded questions and I run the risk of upsetting my friends at Microsoft if I say rude things about this product. I’m also aware that I have only had a very limited amount of time to play with it; that it is still a Preview and will undoubtedly improve a lot over the next few months (however good it is now); and most importantly that I am not the target audience for this product therefore it will be impossible for me to judge how well it works – I know too much, and I am likely to be able to be able to write questions that work much better than a normal user can. I had hoped to try this out on my wife to get some real world feedback but unfortunately I’m travelling at the moment and she’s at home with the kids…

With that in mind, let me give you some idea of how well it works by showing questions I tried and the output I got:

The first question I tried to find an answer for was how many medals Australia had won for Swimming in 2012. I typed “how many medals did Australia win for swimming in 2012” and this was the result:


I had wanted a single value and instead I got a count of medals for swimming broken down by name; they were Australians (I assume) but it’s not exactly what I was after. I then tried “total number of medals won for swimming by Australia in 2012” and hit the jackpot:


My next question “list the cities where the games have been held” got the right answer first time:


A similar question, “total number of times the games have been held in each city”, also worked first time (but maybe because I had learned that Q&A can interpret the phrase “total number” properly):


I don’t know why it thought a map was appropriate here though.

One final test: I wanted to know which athletes had won medals at more than three different Olympics. I tried “who has won medals at more than three games” but got a list of countries that had won medals at more than three games, which of course was quite a long one; “athletes that have won medals at more than three Olympics” got a list athletes and the games they had won medals at. Eventually after several more tries I gave up.

Of course this last question was deliberately difficult question and entering the same text into Google didn’t give any useful results either. I would have been very surprised if I had got the correct answer. I think, though, this question gets to the heart of the reason why a lot of us were sceptical about Q&A when they first heard about it: end users won’t know which questions a product like this can reasonably answer and which ones are just too difficult, and they won’t know how to frame questions appropriately, and therefore a product that seems to suggest it can answer any question you type will always, ultimately, disappoint them. It will be very difficult to set users’ expectations appropriately.

That said, I was pleasantly surprised at its success rate and I’m looking forward to trying it on my own data. To answer my questions above:

  1. Does it work? Yes, I think it does a good job of answering any question I would have expected it to be able to answer. Furthermore, as I said, I know it will improve over time, not only because we’re still in Preview but also because this is a cloud-based solution and MS can keep tweaking after it has been released as well. I wonder if MS are capturing usage metrics and questions and using it to tune the system? I bet they are.
  2. Will anyone use it? This is a much trickier problem. I’ve never come across an end user who has asked for a product like this, but just because no-one has ever asked for something doesn’t mean they won’t love it if they get it. It’s clearly aimed at people who find every other BI tool available today too difficult (and there are plenty of them), but are these people interested in using any BI tool however easy it is to use? Will they just go and ask a colleague or an analyst or a minion to go and find the answer for them instead? Frankly, I don’t know at this point. We shall see though!

Written by Chris Webb

September 23, 2013 at 11:12 pm

Posted in Power BI

Power BI Preview Review

with 26 comments

I’ve had access to the Power BI preview for a week or so now and had the chance to spend a bit of time studying it (there’s nothing like a looming UG presentation to focus your mind when learning new tech), so I thought I’d write a post about what my feelings so far are.

The usual disclaimer applies to this post: while it may sound like I’m being negative at times, that’s just the way I write and overall I’m actually very impressed with what has been delivered. There’s a lot for the Microsoft BI community to be happy about here, and I’ve tried to be as generous with my praise as I have been with my criticisms.

First of all, let me define the scope of this post:

  • We’ve had access to the Power BI Excel components for a long time now, and I’ve already blogged about them at great length so I don’t want to cover their functionality again in any detail. To summarise my feelings about them briefly:
    • Power Pivot/The Excel Data Model: a great foundation for everything else and unlike all of the other Power BI components, quite mature now.
    • Power Query: the new star, as important as Power Pivot in my opinion.
    • Power View: good, but that HTML5 version can’t be delivered soon enough.
    • Power Map: the most recent version is a big improvement, but until we can overlay our own shapes and a few other issues have been ironed out its real-world uses are limited. Great for demos though.
  • That leaves the Power BI cloud and mobile components, and they will be the focus here. I’m not going to bother describing functionality in much detail and instead I’ll concentrate on whether it’s any good or not.
  • I’m sure a vast amount of work has gone on in the background to make the Power BI service robust and scalable which isn’t visible at all, which no-one will even realise has taken place if it all works smoothly, and which I can’t comment on.
  • The Preview still has some limitations, namely:
    • Not all data refresh functionality works yet, although it is possible to set up all the components needed for it.
    • Q&A, the natural language querying/visualisation tool, isn’t available yet. I’ll devote another blog post to Q&A when I get to use it.

Here’s what I’d like to highlight:

The Documentation

It might seem pretty strange to mention the documentation in a review like this, but it’s one aspect of the Preview that I think has been done very well indeed. Usually when a product like this appears the documentation is a bit thin, leading to a rash of blog posts explaining how you actually do stuff. This time, though, it’s clear that a lot of work has gone into the docs so well done to whoever wrote it all up.

Power BI Sites

You can think of Power BI Sites as being an improved way of browsing and organising the BI-related workbooks you’ve uploaded to document libraries in Sharepoint Online (rather of like the PowerPivot Gallery is meant to be in on-prem Sharepoint). They’re also how all the cool extra stuff that Power BI gives you over and above the normal functionality of a Sharepoint document library is implemented. Power BI sites are implemented as an app for Sharepoint; there’s a detailed explanation of what they are and how they work here. Some thoughts:

  • I can understand why it was necessary to implement them this way, and while I don’t think it’s a big issue it’s a shame that there isn’t a seamless transition from normal Sharepoint and Power BI. I can see some users getting slightly confused between a regular document library and the Power BI app.
  • The most obvious example of this is around having to ‘Enable’ a document for Power BI and the related issue of file size limits. John White has already written an excellent post on this topic that I would urge you to read, so I won’t repeat what he says here, but you can see from the sheer length of the post that it’s a relatively complex topic. In addition, one thing that John doesn’t mention is that apart from the 10MB/250MB limits there’s also another limit: if you want to refresh a model by clicking the Refresh button in the Excel Web App, that will only work if your model is below 30MB in size. Since it’s not clear what the size of a model is from within Excel, I suspect that users working with large data sets will run into these file size limitations without any warning and not know what to do. There are some best practices that should be followed (like using Power Query to load data direct to the model, and not staging it in the worksheet) but I’m not confident that end users will remember to follow them. Hopefully a future release of the Workbook Size Optimizer will help here.
  • As I said in a previous post, I like the fact that Microsoft has spent some time thinking about the role of the data steward and implemented some monitoring and management functionality appropriately. I hope they continue to build more functionality in this area, because self-service BI implementations will live or die by how well the data steward does his or her job.

Data Refresh

As I said, not all data refresh functionality works yet but I’ve managed to go through all the steps to set up the data management gateway. The actual set up was straightforward and it seems like it’s a good solution to the whole problem of refreshing workbooks in the cloud against on-prem data sources. Initially only SQL Server data sources are supported for direct refresh, which is understandable, and I guess more data sources will be coming soon; the workaround for other data sources described here of using SSIS to publish an OData feed and then accessing that via a Linked Server seems a bit of a hack to me. I also like the way registered data sources can be exposed as OData feeds from Power BI, but that’s because I like OData.

Power Query Sharing

I’m starting to realise how important the ability to share a Power Query query between several workbooks you own, and with your colleagues, is in Power BI. It’s going to be interesting to see whether end users create and share queries amongst themselves or whether the data steward or BI professionals create the majority of queries and then pass them down to the end users. I also wonder whether it will be possible to create a series of relatively simple Power Query functions, share them and then use them as building blocks to create more complex queries. This is something I want to come back to in a future blog post, though, once I’ve had a chance to think about the implications of it some more.

Mobile BI

Ah, mobile BI at long last! But is it any good? Hmm, well, yes I think so, or it will be when it’s finished; I don’t have a lot of experience of mobile BI apps though so I can’t claim to make an informed judgement. Thoughts:

  • I don’t own a touch-enabled Windows device of any kind, but I installed it on a Windows 8.1 VM and used it with a mouse with no problems (even though I subsequently read in the docs that it’s not supported on Windows 8.1!).
  • It’s very simple to use. Which is good.
  • It also seemed very responsive on the reports I tested it on. Which is also good.
  • While navigating through the Power BI site folder structure to find your reports feels a bit clunky, this is only something you’re going to do once or twice before you add the reports you look at most to your Favourites.
  • Some Power View functionality isn’t supported yet in the HTML5 view that the app uses, so for example Scatter charts can’t be displayed, but that will be addressed soon I’m sure.
  • There are no native iPad or Android apps as yet. I really hope they are coming soon. At the moment the docs say that you have to make do with the HTML5 view in the browser, but I think a dedicated app is important.
  • The way the each Power BI workbook is broken up into parts and displayed as a separate report in the mobile app will take a bit of getting used to, but my initial reaction is that it’s a reasonable approach to have taken despite its limitations. By default the mobile BI app shows each individual table/chart/PivotTable/PivotChart/named range and Power View sheet in a single workbook in a Power BI site as a separate report (see here for more details), and while you can control what does get shown you’re going to have to make a bit of extra effort to do this. Once again the docs and John White have some good information on how to design and configure reports with the mobile BI app in mind.
  • My feeling is that Power View sheets are going to be the best way of delivering reports through the mobile app – from my experiments they seem to work an awful lot better than PivotTables etc when viewed through the mobile app.

Is Anyone Going To Buy It?

From a purely technical point of view, Power BI is a worthy suite of tools and deserves to be used. In terms of functionality I doubt whether Power BI will ever be able to compete with the pure-play self-service BI vendors but I don’t think that is Microsoft’s strategy. As I’ve said here before, Microsoft is (very wisely) building on the familiarity and ubiquity of Office and in particular Excel, so while Power BI might lack some specific features, customers will want to use it because they already have Excel skills and don’t have to learn something completely new. Why pay a large amount of money for QlikView or Tableau licenses if you’re going to move to Office 365 anyway and everybody already knows Excel?

However, I have a bad feeling that if Power BI fails it will fail not for technical reasons but because customers don’t understand how to buy it. To explain some more:

  • At a fundamental level, a lot of people still don’t understand what Office 365 is. And I don’t just mean customers here, but partners, MVPs and even Microsoft employees. They have heard Office 365 is something to do with the cloud and they think that it means you have to use Excel, Word and so on in the browser. They are surprised to learn that it’s just a different way of buying Office, and that you still get the desktop Office tools.
  • Once you’ve got over that hurdle, you then face the fact that there are too many SKUs for Office and Office 365 to choose from. Again, many customers, partners, MVPs and Microsoft employees I’ve talked to struggle to understand which functionality is in which SKU. The long post I wrote about BI functionality in Office and Office 365 earlier this year, before Power BI came along, just goes to illustrate this complexity.
  • Bring Power BI into the equation and things are even less clear. What is Power BI exactly anyway? It turns out that it’s yet another one of those vague marketing terms (see also “xVelocity”, “BISM” and “UDM”) that Microsoft comes up with to try to bring some kind of thematic consistency to a bunch of related tools – you can say that it relates to almost all the BI functionality in Office, some of which has been around a long time now, some of which is newly released with this Preview. Under the banner of Power BI come some Excel addins and a cloud service, but the licensing terms and system requirements aren’t all the same. Looking at the just Excel components:
    • Power Pivot is available as a free download for anyone using any version of Excel 2010. The Excel Data Model, the engine of Power Pivot, is built into all desktop versions of Excel 2013, and the Power Pivot addin (which gives you the same functionality as Power Pivot in Excel 2010) is only available if you are using Office 2013 Professional Plus or an Office 365 SKU that includes Professional Plus, or soon the standalone retail version of Excel 2013. Power Pivot for Excel 2010 requires Windows XP SP3 or greater; Office 2013 will only run on Windows 7 or greater.
      The change in licensing between Excel 2010 and 2013 was, of course, the trigger for a lot of protests earlier this year. Whatever rights and wrongs of the licensing, though, the change itself is the cause of a lot of confusion: I’m still hearing about people who have installed the wrong SKU of Excel 2013 and find they can’t use the Power Pivot addin.
    • Power View Excel functionality is also free, but only available to users of Excel 2013 and with the same restrictions as Power Pivot – ie you need Professional Plus or standalone. Power View in Excel is, of course, closely related to but not the same thing as the standalone Power View app available via Sharepoint.
    • Power Query is available to users of Excel 2010 and 2013, but only for those who have Office 2010 Professional Plus or Excel 2010 standalone or Office 2013 Professional Plus, an Office 365 SKU that includes Professional Plus or Excel 2013 standalone. Note the subtle difference between these terms and the Power Pivot terms – Pro Plus is required on both 2010 and 2013. Power Query is only supported on Windows Vista or greater.
    • Power Map is only available to users of Excel 2013, with the same restrictions as Power Pivot and Power View – Professional Plus or Excel standalone.
  • Turning to the Power BI cloud components, no licensing details have been announced yet but I assume they will be licensed separately and will require you to have bought the right SKU of Office 365 first.

In an ideal world, there would be just three SKUs of Office – Home, Small Business and Enterprise – and Power BI would not be an optional extra but would come as part of the Enterprise SKU. That isn’t going to happen, though, and Microsoft can’t afford to give away too much stuff for free any more alas. I’m still hoping for some kind of simplification of the licensing but maybe I’m being naive. At the very least some very clear and concise guidance will be needed to help customers understand the prerequisites and licenses necessary for each of the Power BI components. For example a customer might want to use Power Map, know that Power Map is part of Power BI, hear generalised statements like “Power BI needs Office 365”, and therefore think they need to have an Office 365 subscription to use Power Map – which is not true. Microsoft needs to get to the point where it doesn’t take a long and complicated blog post from someone like me to explain all of this.

Of course the dependency on Office 365 for the cloud-based components remains controversial. I’ve been explicitly told by Microsoft that the rumours that there are no plans to incorporate Power BI cloud features into Sharepoint on-prem are wrong. Features will show up in the cloud first, and while it may prove to be difficult to implement some features on-prem, in general most features will make it to on-prem Sharepoint eventually. In any case, I think enough customers will move to Office 365 for Power BI to gain some traction, notwithstanding all of the very valid worries companies have about storing data in the cloud and the general inertia around upgrading Office in large organisations.


I think Power BI has the potential to make Microsoft a player in the self-service BI market and to re-energise the Office platform. If Microsoft can keep up the momentum with development and if it can persuade its customers to move to Office 2013/365 and the cloud then it will at last be able to take full advantage of Excel’s gigantic user base to bring BI to the masses. There are a lot of “ifs” here though, and the current mess of Office SKUs and licensing could strangle it at birth. I hope this doesn’t happen.

Written by Chris Webb

September 16, 2013 at 6:58 am

Posted in Power BI

New Version Of Power Map Available

with 7 comments

Last week a new, preview version of Power Query was released to work with the Power BI public preview (John White, whose blog has a lot of good Power BI information, has the details here); today, a new version of Power Map was released too. You can download it here:

Here are some of the new features, in no particular order:

  • You can now overlay certain geographical regions onto a map. For example, with the following table in the Excel Data Model:

    In Power Map you can create a new layer type of Region and see each country shaded by their sales value:


    The Region shapes are sourced from Bing; you can’t upload your own shapes unfortunately. I have no idea what regions Bing does know about, but it clearly knows about English county boundaries (though it doesn’t know about postcode boundaries):


  • You now have the option of seeing a ‘Flat Map’. Here’s the map above shown in flattened form:


  • You can now control the colours used in a layer:


  • You can add annotations with images in:



  • You can now record tours from within Power Map and save them to an MP4 file.
  • Calculated columns and hidden columns can now be referenced in a layer.

There are plenty of other changes – I’ll update this post if I’ve missed any other major ones – but in addition the app seems smoother and faster, as well as being (slightly) easier to use.

Written by Chris Webb

September 11, 2013 at 10:48 pm

Posted in Power BI, Power Map


Get every new post delivered to your Inbox.

Join 2,868 other followers