I’ve been experimenting with Power Query and the rest of the Power BI suite in Excel (desktop edition) since it was originally released. But, like most folks that aren’t completely centered in that part of the data-based offerings, the plethora of new functionality and new releases made it difficult to keep up. The online help has improved, but the distinction between Office 365 Power BI and Power BI desktop and the fast release cadence (updates appear almost monthly), made it difficult to write books (which require pre-production time). Having met Chris Webb at a few conferences, when I heard he stepped up to this challenge with a Power Query book entitled “Power Query for Power BI and Excel“, I was interested in reading.
The book weighs in at 272 pages, and I thought it might take a couple of weeks to do it justice. Once I started however, I was hooked and consumed it in a couple of days. Chapter 1 was a nice introduction that told the story of Power Query’s reason for existence, and described the differences between the experience with different versions (Office 365 Power BI vs Excel 2013 vs Excel 2010). It wouldn’t be too big of a stretch to call this an incantation of SSIS-like functionality for self-service BI. Reading quickly through chapters 2-4, about data sources-transformations-destinations was a thorough demonstration of the functionality of the product, and clarified exactly what was supported. I was introduced to the “Excel Data Model” concept here as well.
It was the chapter on M that was the gem of the book for me. I’d realized that you could extend your Power Query queries with M functions, but didn’t realize that every query was actually a script written in M. Chapter 5 describes M the language and how you can use it to not only embellish Power Query queries, but to even write them by hand. M code that comprises Power Query queries looks vaguely reminiscent of Hadoop Pig scripts, although obviously the surface area isn’t the same. I was impressed by the author’s treatment in “Introduction to M”.
The chapter on Power Query in Power BI Office 365 told me what I wanted to know about how Office 365 extends the functionality of Excel desktop Power Query into a shared, more enterprise-y offering (e.g. query sharing), as well as how it integrates with Sharepoint. And the Multiple Queries and Power Query Recipes chapters helped get me into the “thinking of solving problems with Power Query” mindset. The first sentence of the recipes chapter “…when you are learning about a new tool it isn’t enough to know what each button does when you press it, or what each function returns when you call it” was right on target.
I had a few minor quibbles. I’d like to have seen a mention of the exact version of Power Query (version number and release date) that the book covers. I knew that new releases are coming out monthly, and was working with the version that appeared a few days ago, but looking at a screenshot that said “Apply and Close” when my version said “Close and Load” (as well as other differences) in Chapter 1 was a bit disconcerting. But that’s more of a “don’t constantly change the GUI on something that’s supposed to be consumer-focused” comment on the product. Or maybe it looks different in the Office 365 version, I didn’t know.
And about the product…I was also surprised, product-wise, that they didn’t support OLE DB (at least for the Analysis Services OLAP provider), or even ODBC, although you can shoehorn these in via the vanilla Excel “data sources” tab. I guess it was to keep the GUI rich and consistent, functionality-wise, but if you can do it for OData… I did especially like the non-traditional data sources like HTML tables/pages and Facebook, although a generic JSON data source would also be useful. Maybe I just didn’t see where the generic JSON functionality lives. The “data sources” book chapter did make the level of support very clear, though.
Overall, I’d recommend this highly recommend this book if you’re getting started, or even if you think you’re pretty far along with this tool. The M and Office 365 Power Query chapters themselves may be worth the price of admission.
Cheers, Bob (@bobbeauch)