When reading horror-stories about Excel-hell describing how dangerous it is to use Excel in corporate environments, I cannot help but to think of this hilarious video describing the fatal consequences of acting without common sense: Just don’t do stupid things with it.
Although Excel comes nearly for free (in relation to what value it delivers) this doesn’t mean that you don’t need to invest in applying proper techniques (like in any other profession). There’s training and best practices for every different need.
But the best thing about Excel seems largely unknown still: Since the invention of Power Query it has never been easier to be save around Excel than before: A magic tool that can solve many of the problems that cause Excel hell: Repetitive tasks: The little adjustments and extensions that pile up when you use your workbook again and again and are often performed without realizing the (meanwhile complex) context of all the standard-Excel-elements involved: Power Query will prevent this mess. It will help you organize and automize your repetitive tasks in Excel.
So if you’re experiencing troubles in your periodic workbooks or find yourself repeating same steps again & again every month, I know no better introduction on how to solve these problems than what Ken Puls and Miguel Escobar have put together in their brand new book. If you prefer video: Check out Bill Jelens stunning quick intro on Youtube.
Further valuable resources: Chris Webbs book is also an excellent source on get you starting, although a bit out of date due to the fact that Microssoft’s product team are releasing monthly updates for it (which is absolutely great!) it still covers all basics very well and is definitely worth reading. On his blog you’ll also find tons of in depth knowledge around Power Query. Ken and Miguel also offer brilliant courses and on their blogs you’ll find some more cool tricks on how to use Power Query.
And if you’re dealing with large tables, Power Query will feed easily into Power Pivot which enables you to deliver reports in stunning performance. The one-stop-shopping site for this fantastic tool is PowerPivotPro where Rob and his team and guest-bloggers have put together an abundance of resources where one tops the other. My personal favourite is their PowerPivot University.
So far on how to use these tools as advertised. But I’d like to close my declaration of love (should better keep my emotions under control) thoughts on this red-letter day with an outlook on the creative “misuses” of Power Query that are founded in it’s ability to output in form of standard tables instead of pivot tables (like charting and dynamic reports) and its rich language repository. Just to give you a glimpse of Power Queries’ creative potentials, which I’m going to reveal on my blog here. So stay tuned.
Happy Power-Spreadsheet-Day!
Enjoy & stay queryious 🙂