Our Blog

where we write about the things we love



Power BI from Microsoft

A new platform and tools to enhance Business Intelligence – on the desktop, web and mobile.

At the recent Microsoft Worldwide Partner Conference (WPC) Microsoft announced the new Power BI it is developing. Power BI will provide a useful set of tools that complement the existing offerings to increase the breadth and capability of the Business Intelligence stack on the desktop and – looking forward – the web and mobile space.

Power BI will be a platform for delivering Business Intelligence across multiple devices and mediums; plus we will see the incorporation of features such as Excel plug-ins, including “Data Explorer” (now called “Power Query”) and “Geoflow” (now called “Power Map”). In recent times these have been available as plug-in components for Excel, and – although they seemed to remain fairly low key – they provided a useful and reasonable enhancement to the capabilities of the Microsoft “Personal BI” and “Team BI” stacks, enhancing the analysis and visualisation of information.


A richer and more interactive web and mobile BI experience

Even though these features have been refined and improved since their initial appearance, they were only available for the desktop user, and Microsoft has found itself lacking in a nicer, richer and more interactive BI offering on the web or for mobile platforms. The good news is that Microsoft appears to be addressing this gap by introducing “Power BI” to Office365 as well as an upcoming dedicated app which will render the published Office 365 documents. Power BI will include the Power Query and Power Map components and capabilities you see on the desktop, as well as some great additional functionality highlighted by this video. This should provide something to fill the mobile and web-based BI gap Microsoft has faced and will be interesting to review when it is released.


When will Power BI be available for use?

According to what was announced at WPC, Power BI will be made available for preview later on in the year, although a date remains unspecified. You can register for the preview and download the Excel components we are discussing here.


Features and functionality

With these less well known features now brought to the fore, I thought it’d be a good opportunity to share some of the features and functionality within these tools that I find make them a valuable add-on to your current Excel interface, as well as highlighting them as components which sit inside the Power BI suite.


Power Query

Power Query is a plug-in available for Excel 2013 Professional. It provides some great functionality on top of Excel’s native ability to harvest information directly from the internet and a wider range of data sources.

With the amount, variety and accessibility of data increasing, businesses are investigating how to leverage this information to their advantage. The great thing about Power Query is that it empowers the data analyst to be able to do more, but still only requires the tools and skillsets they already possess. A familiar interface, desktop tools and confidence in the easy to use Power Query plug-in mean that analysts can begin to explore and consume a wealth of data previously unavailable to them.

The real benefit I see to this tool is that it provides an effective mechanism for harvesting public data from outside of the organisation. These can then be included when prototyping models and performing some pretty powerful ‘Personal BI’.

Of course, by leveraging other Microsoft tools such as Power Pivot and SSAS it is a simple exercise once the data model and contents have been validated as fit for purpose by the business, to promote the model and data into the managed corporate data environment where they can leveraged across the enterprise.

I feel there are five main areas that highlight the benefits of this plug-in:


1. The ease to discover, access and consume the world’s data:

Power Query brings the concept of a data search to Excel. Using a simple interface you can search public data in an easy and straightforward manner. You are presented rich and interactive previews of data; and when you have found the data you need, a single click will import the data into your workbook. The appeal to this is that you can now scour public data sites, including the likes of Wikipedia, and the data is presented back to you in a nice tabular format.

I have a couple of reservations with this functionality, though. The first is some of the search terms entered present some unusual results and trawling through pages of returned matches can prove time-consuming. The introduction of a relevance score to search results would be a nice addition to future releases. The second being that in the current version of Power Query, the bulk of the public searches appear to pull information from Wikipedia. Branching this out into a true search capability like Microsoft’s Bing search services and corporate information as promoted by Microsoft at the WPC will be a future enhancement that would prove very useful. But don’t let this detract you from using what is a very good feature.


2. Hassle-free data conflation

Power Query makes it easy to combine data from different data sources. Providing you have a common and unique identifier for your data that you are downloading, such as a list of districts, using the merge feature or append features, you can assemble the relevant pieces of data from multiple datasets together to provide a single master table for the data you are interested in. Again, this is all controlled from a simple, easy to navigate interface that encourages the adoption of this tool. Combined with the online search capability outlined in the previous point, conflating data in this fashion will no doubt prove to be very enriching for many organisations. It will, for example, allow them to search for market trends over time, country by country comparisons, and data that they may not otherwise be aware of or otherwise have easy access to, and combine it with data from inside their organisation.


3. In-stream data modification and manipulation

Power Query makes it easy to reshape and manipulate your data. This is especially useful when combined with the online search function. Everything from basic requirements such as filter and sort, to the more complex operations such as un-pivot are all achievable with just a few clicks. You can also edit components within columns, making conformance to a data model or standard within your company straightforward at the point of data import. Another nice touch is the “Steps” tab at the side of the screen. This identifies individual data transformations and modifications applied to the data and the order in which they have been applied to arrive at the current data state shown on the screen. From here you can modify or delete a single step without having to start over from the beginning of the process.

By right clicking you can expose the most useful or common transformations, but there is also the ‘Power Query Formula Language’. This is similar in appearance to the Excel formula bar and expresses a code version of the filters and manipulations you have applied to the data. More advanced users may wish to enter transformations here using the ‘Power Query Formula Language’ allowing for more customised transformations and expose the real power of the transformation and manipulation capabilities.

The best bit about this is that any transformation is happening in-stream, between the data source, and the result you desire to see on your screen. This means you are only ever consuming the data that you want to see, and how you want to see it, and it can help to minimise the size of your Excel workbooks and data models.


4. Expanding your Power Pivot models

Another benefit compared to the standard data import functions, especially when using data from the internet, is that the data is more structured, making it easier to integrate into Power Pivot models. This is facilitated with the one-click option to load the data into your Power Pivot model.


5. The connectivity capabilities to other data sources

Power Query supports access to a breadth of data sources, ranging from the simple such as flat files like text files, to sophisticated or complex sources like Hadoop for big data. 

The connectivity capabilities to other data sources

Power Query provides an effective tool for rapidly gaining access to information outside of your organisation. This empowers the analysts to quickly enrich the analysis of your business and understand a number of external factors which may have influence over the results you are seeing. This is quite a useful tool – I recommend checking it out.


Power Maps

Power Maps is another plug-in available for Excel and provides a very appealing way to visualise data geospatially and over time in 3D. With an interface that is clean, easy to use and the ability to set up animated tours of your data, Power Maps can readily be used as another presentation medium without the user needing to be an Excel expert, or running the risk of the audience switching off from looking at spreadsheets full of numbers or your regular charts.

The platform presents a nice blend of basic spatial representation and visualisation techniques, with traditional charting and web based services such as geocoding; so that you can plot your data on the map without having to worry about coordinates and map projections (providing you have an address component in your data). If you do have coordinates already, these can be used directly by the software providing they are in the correct latitude and longitude decimal degrees format.

The direct analytics capability of the tool is fairly limited; you are not able to slice and dice the data as you would through other tools in the Microsoft stack, but the real impact of this tool is noticed when being used to support a presentation. The ability to create fly-throughs and animated tours of key focus points of your data over time can be a very powerful way to support the message you are conveying to your audience. Combined with the ability to infer new trends within data or between data sets by looking at spatial relationships over time adds another new dimension to the data analysis. Other nice features are the dynamic selections you can make on the bar charts which select the corresponding data on the map similar to what you see in Power View, and makes the identification of specific data elements easy.

There are some slightly clunky elements in this release of the application. One example is that, despite having the capability to present different layers of data, if you use a common set of coordinates or geocode to a geographic centroid-like city all your data appears on top of each other. This can be negated by simply modifying the coordinates if you have them. It would be a really nice feature in future releases if different data layers offset themselves from each other slightly if they have a generic geocode.  The current release is also only really designed to look over tables rather than more advanced data representations such as aggregations in tables or pivot tables from databases.

Having said this, I have been able to generate some fairly small-scale detailed representations using the tool and have been happy with the results. It is certainly another tool within the BI stack worth examining if you are looking for a new way to present and discuss the information you have uncovered. 

Power Maps BI

Power Maps BI



Posted by: Russell Bowden, Business Intelligence Senior Consultant | 30 July 2013

Tags: Business Intelligence, Microsoft, Mobile, Office 365, SQL Server, BI, Excel, WPC, Power Query, Power BI, Power maps, Power Pivot

Blog archive

Stay up to date with all insights from the Intergen blog