Power BI–the awesome expansion of Excel!

Excel has been the dominant tool for analysis and reporting for a long time now, updates and improvements have been “software normal” until recently. However, in a rare “hop,skip and jump” Microsoft surprised us with some exciting changes.

In Office 2010

1. Good old Pivot Tables went from “user hostile” to “user friendly”

2. In the background Power Pivot was released as a free add-in

With Office 2013, and 365+, the Power BI suite developed and this is where new ground is being broken.

Power BI consists of 4 parts:

Power Query – for grabbing and loading data from a very large number of possible data sources

Power Pivot – for modeling and analysis of the data

Power View – for visualizing and reporting

Power Map – for geographical visualization

The engines

Power Query and Power Pivot are the power plants, this is were the data is structured, crunched and turned into useful actionable information.

Today I will spend some time on Power Query since this is the starting point for most of us working in the Power BI suite.

First of all, I have never written a database query and I am still not able to do that. I have over the years spent enormous amounts of time cleaning, flattening and formatting data for use in Excel. Eventually I learned to write macros that would handle some of the repetitive and tiring tasks, but there were still plenty of hands-on pounding to get raw data into Excel shape.

Today, thanks to Power Query, I can retire most of my macros and I can use my time n-fold more efficiently. So, what is the difference?

Power Query

gives me, the database illiterate, the ability to grab and shape data for use in Excel or Power Pivot with a minimum of effort. The interface is user friendly, the steps are easy to follow and I can get started without having to learn some strange programming language. The language is there and it’s called M (giving me some Bond vibes), in time I will undoubtedly learn the basics, but the initial time investment has been eliminated. Every query I create in Power Query is saved in the workbook where it was created and can be reused time and time again using the “refresh-button”. It can be copied and used in other workbooks and as long as the data source remains stable so will my query.

Rob Collie (http://www.powerpivotpro.com/) wrote that there are two categories of people concerning themselves with data, Report Producers and Report Consumers. Both categories will benefit from Power BI and Power Query. For report consumers the benefits will be in speed and data integrity and for report producers there will be huge amounts of time saved when the amount of hands-on data pounding is replaced by a simple one-click refresh. Being in the producer category myself I must confess that I am Skrattar and Förvånad!

Power Query comes to us from the SQL Server planet in the Microsoft universe and I am very grateful that Microsoft has decided to break down walls and open up new roads for us simple Excel folk!

Some good reads, available to us Swedes at AdLibris:

Power Pivot Alchemy by Rob Collie and Bill Jelen

Power Query for Power BI and Excel by Chris Webb

I will be back with more thoughts and some examples in the future.


About powerpivotseth

As long as I learn, I live, and to that end I am now immersing myself in the new Microsoft Power BI applications available through Excel. I was quite good at plain vanilla Excel, but even so, I ended up in "No, that is not possible" dead ends at times. Then I discovered Power Pivot and all my previous dead ends opened up into veritable eight-lane interstate highways!
This entry was posted in Power BI, Power Query and tagged , , , , . Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s