It may have a funny title, but this will be one of the most important Excel books you ever buy in your career.
Written BY Excel pros FOR Excel pros, this book has been designed to guide you through learning how to master the new "Get and Transform" data experience in Excel. Released as a free add-in from Microsoft for Excel 2010 and 2013, Power Query technology is now built in to Excel 2016 and the Power BI Desktop application. This technology is a game changer, and will revolutionize the way you work with your data forever.
Way back when this book was nothing more than a concept, we knew that it needed to be approached in a specific way. It had to speak to Excel users, the problems that they face on a daily basis, and ways to solve those problems both effectively and efficiently. It had to be written to keep you engaged, and to take you on a guided journey, learning from someone who understands you as an Excel pro, how you work, and what you face on a daily basis.
We're proud to say that we believe that we've delivered on that goal, starting with common tasks you perform, and back filling them with real world issues, inspired by a wide variety of real world data sets. Packed with downloadable files and step by step instructions, this book is built to help you master Power Query so that you can use it in your own work, with your own issues. This book was designed from the beginning to get you up to speed quickly, and to give you the knowledge to build awesome Excel solutions quickly.
What is in the book?
These are some big claims, to be sure. So how do we do it? What will you find between the covers of M is for Data Monkey?
We begin with the most common issues facing you on a daily basis:
- Importing, cleaning and automating the refresh of data from your files
- Dealing with common issues, including importing data with different date formats
- Appending two or more data sets (from different files or from the same workbook)
- Importing and appending the data from all files in a folder... easily!
- Dealing with difficult data in the shape of non-delimited text files
And that's just the first few chapters. Each technique is illustrated with real world problems that you face in your job every day, and generates real world solutions to those problems. Just these few chapters alone will show you how to connect, clean up and re-shape your data for consumption faster than you've ever been able to do before. And then, to top it off, you learn how to automate it all so that you can just click a button to refresh it next time you need it. And the best part? It's EASY!
But that is far from all there is in the book...
We show how to use Power Query to connect to and retrieve data from a database, and even illustrate how to connect to SSAS instances as well. But let's face it, not all Excel pros have access to these data sources. And if you don't? We even provide you with access to an online SQL Azure database, so you can still practice, and maybe even inspire the IT department to grant you the access you need.
But in the meantime, we all know that data isn't all contained in nice databases, all ready to be used. In truth, it's far from that. , so we also show you how to connect to all of the following data sources as well:
- Text and CSV files
- Excel worksheets
- Excel files
- Web pages
- Microsoft Exchange (do you know how much business data is stored in your emails?)
- Other Power Queries
Knowing each and every one of these sources is incredibly powerful, as this means that you can start linking your data from disparate sources. Using Power Query you can source data from multiple places, landing it in Excel and/or Power Pivot to build truly robust solutions.
Of course, your data rarely every arrives in a nice, clean and ready to use format. For this reason, we also put a lot of focus on how to re-shape your data (dealing with issues ranging from easy to more complex.) Between the covers of M is for (Data) Monkey, you'll learn a variety of techniques for cleaning and re-shaping data, including (but not limited to) the following:
- Un-pivoting data with only a few clicks
- Merging tables together (with no VLOOKUPs)
- Grouping and summarizing data
- Transposing and un-pivoting complex data (because let's face it, life throws us really crappy data on occasion!)
"Wow", you're thinking... "there's a lot in this book!" And there is, but we're only part way through the list. Everything illustrated to the point above is done using the built in user interface, clicking buttons. But we go deeper than the standard user interface... much deeper.
The book includes five chapters dedicated to understanding and working with "M", the language that runs under the hood of the Power Query engine. While you don't need to ever learn this language, you'll find that if you do, you can build some incredibly diverse and robust solutions. For that reason, our chapters carefully guide you through the journey of learning this new language, and then applying this knowledge in useful scenarios. From learning basic Power Query formula structure, to implementing conditional logic and then a deep dive into Power Query language syntax, these chapters prepare you for creating custom functions in Power Query.
And why are custom functions important? It's because they unlock these three amazing chapters that will change the way you work with your Excel solutions and Power Pivot data models:
- Advanced Conditional Logic
- Dynamic Parameter Tables
- Dynamic Calendar Tables
These are truly game changing techniques that will allow you to build robust and dynamic solutions quickly and easily. And that is the key. Other technologies may allow you to do some of the things listed above, but the time needed to learn these tools is immense. With Power Query you'll be skilled up and building in a fraction of the time.
The impact is bigger than JUST Excel...
M is for (Data) Monkey IS the Excel Pro's Definitive Guide to Power Query. Written by Excel Pros, for Excel Pros, but it's bigger than just Excel. Power Query technology is now being used as the default "Get Data" experience for not only Excel, but for Power BI Desktop as well. So the skills you learn in this book will not only help you Master Your Data in Excel, they are also portable to other emerging technologies as well.
This technology is a game changer. And this book will get you there.
Get your copy of M is for (Data) Monkey
There are three ways you can get your copy:
Buy a digital download copy direct from the author and get it RIGHT NOW
Get it for FREE when you register for our Online Power Query Training Course
Buy a physical copy of the book from Amazon.com
Let us help you change your life. Buy our book. Master Power Query. Master your data!