Advanced Power Query and other questions from ‘M is for Data Monkey’ readers

The #1 Power Query book in sales and ratings. THANK YOU everyone

A little over a year ago, Ken Puls and I published a book called ‘M is for Data Monkey’ which came out from a cycle of iterations over our Live Online Workshop. The book, in essence, is a summarized version of our Workshop with some Key concepts and examples that we used back then in our Power Query Training Workshop. Of course, Power Query has changed and our online workshop has changed as well. We keep our workshop fresh and up to date with the latest and the greatest, but the book is still relevant and will keep being relevant for many years to come even if some of the new UI or feature changes are not reflected in the book.

It’s crazy how well our book as been received across the globe with over 4.5 stars average on most of the reviews that we receive. Here’s a few of the reviews that some of the readers have left on the Amazon US site:

We thank everyone that has received a copy of our book and, to those that have yet to acquire the book but still want to learn a few Power Query Magic Tricks, we’ve recently published a completely free eBook called ‘Magic Tricks for Data Wizards’. All you need to do to get this free eBook is sign up to our newsletter and that’s not the only reward that you’ll be getting when you sign up – you can expect much more!

A quick summary of our book ‘M is for Data Monkey’

One of the things that both Ken and I monitor is the feedback that we get from the readers of this book. We usually monitor our disqus forum here and different review sections across all the major book sales sites (like Amazon, Google Books and others). I wanted to make this blog post a while back, but I learned that sometimes its better to act than to write and that’s how the free eBook (Data Wizards) came to life and now it is time to write and talk about some of the main questions that our readers got after reading the ‘M is for Data Monkey’ book.

Before tackling the questions, I wanted to give you a quick summary of what our main goal with this book is: To help you have the best foundation and understanding of Power Query as your new tool. Basically, how to think and tackle your situation using Power Query as your tool of choice.

Now, how did we make our point across? We mainly use practical solutions that would resonate on Excel users that have had the same data manipulation problems that we’ve had in the past, and how we can solve those problems with just a few simple clicks in Power Query.

What’s the scope of this book? We cover Power Query (its UI) and how it works and then lay down the basic concepts of the ‘M’ language so you can have a good foundation to understand not only its syntax and objects, but also be able to read the documentation that Microsoft provides and then start writing your own ‘M’ formulas. There are over 600 functions in Power Query and neither Ken nor myself memorize those functions and we don’t recommend that you memorize them either.

Rather than memorizing those functions, we show you how you can find the exact function that you need based on how these functions are structured in the ‘M’ language (Did you know that all the functions related to numbers have the Number.Xyz prefix?). Once you find this function, you can actually read the documentation and understand what it does (Finally understanding what those curly and square brackets mean).

‘M is for Data Monkey’ talks mainly about how to tackle situations using Power Query and how to get the best out of it from the UI itself, but we also cover the ‘M’ language to a point that will not go over your head as we wanted the content of the book to be something that will ‘stick’ to you and that you’ll be able to apply right away to your daily work and get value from it. Of course, for the most advanced users who wanted to find out how to do more advanced scenarios we have several chapters where we cover the ‘M Language’ and things like ‘Custom Functions’.

For someone who is looking to go beyond what the book offers then we have our Power Query Training Online Workshop where we have definitely gone WAY beyond what our most advanced scenario in our book has to offer. A clear example is the Chapter 6 from our free eBook ‘Magic Tricks for Data Wizards’. Be sure to get your copy here.

On to the good part of the blog post. The FAQ from our readers

Here’s a few of the main questions that you can find from most of the review sections of these sites:

• I wanted to find out more about Web Queries, but the book does not go too deep into it – We often get this question or comment from many of our readers and, quite frankly, the Web Queries is a topic that is still something that needs to be reviewed on a case by case scenario as each situation depends on how your html looks like and how the url was created, which would imply that we go deeper into HTML basic to intermediate knowledge and even talk about JavaScript concepts. Specific connectors for Online Services or Web Queries is something that is being currently developed by Microsoft itself, so we chose not to go too deep into this as it might become completely obsolete in a near future and we are glad that we made that choice. The Power Query team has been hard at work creating connectors for the most popular online services like MailChimp, Google Analytics and others which were not available at the time that we wrote this book – and yes, there are more connectors coming and perhaps even more extensibility coming so you can connect to a web source more effectively. Apart from API’s, you could be using another function called Web.Page in Power Query which is the one that we used in our book to get the data from a CIA page for the World Fact book. More often than not, these websites offer an API or another authenticated way to get the data, but with Power Query you’re unable to set your own Automated Authentication workflow to get a auth key, auth token and refresh token – this is quite possibly something that the Power Query team is working on in terms of extensibility for SQL Server vNext (I hope!).

Our goal with this section of the book about Web Queries was to showcase what Power Query does which is simply read the link and interpret the content of that url. If it’s a file then it’ll open a file, if its an html document then it’ll read the document and look for tables tags to display any tables that it finds and if you need anything specific from that html, then you’ll need to do the Web scrapping based on how your html document looks or work on how to create an specific url that will provide the exact data that you need (for websites that use javascript to display tables, for example).

Connectors created by Microsoft for popular Online Services. These are essentially API Web queries using the Web.Contents function from Power Query

• I want and need to know more about the ‘M’ Language –  we’ve heard you loud and clear! the ‘M is for Data Monkey’ book gives you all the basics, intermediate and even some advanced knowledge of the ‘M’ Language from a conceptual and practical standpoint, but you need more because you know how awesome Power Query and its language are. That’s why we’ve created this new free eBook so you can see examples of how we put that ‘M language’ to good use in combination with Power Query’s UI. Remember that a good foundation is key in understanding any language, and we know that after reading our book you’ll be able to read this table message

Do you have any specific scenario that you need help with?

We’re always looking for new and exciting ways to put Power Query to the test, so if you have any specific scenario that you can share with us then be sure to contact us through our contact form here.