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
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?
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 and !
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.