Master Power Query with our book - M is for Data Monkey

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,531
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.

Learn more about the book here, and acquire the skills that will let you answer questions instead of asking them!
 
Do you have this book in portuguese?
 
Old thread - still a relevant book?

The book sounds great, the only problem is its date of issue. Is this book a 2016-edition that will show functions that have changed, if so, to what extent? I want to develop my skills/solve present problems, and are working with Office 365 Business. Are there any other advice of how to learn, for example also references to special thereads on this site, Please post.
 
Hi Spangamamman,

M is for Data Monkey was released using the version of Excel that became Excel 2016. What this means is that newer features (merging options, the revised combined files, conditional logic wizard and column from examples) aren't in the book as they didn't exist. Having said that, over 80% of the text still remains relevant today.

We are working on a new version, but it likely won't be out until 2nd quarter of 2019.

As far as other resources, the one that we focus on is the Power Query Academy. This includes over 13 hours of Power Query training that takes you from newbie to master, includes the current version of the book (and next when it is released), as well as the Power Query Recipes that we've been working on. It's obviously not in the same price bracket, but it's the most comprehensive Power Query resource you'll find out there.
 
Hi Ken

I'm working through your "M is for Data Monkey" book and finding it very useful indeed.

I have, however, had a problem accessing Azure Adventureworks . I've followed your instructions in c8 and tried to access adventureworks using the credentials from your book, i.e.

Username: DataMonkey@ptypanama
Password: D4t4M0nk3y!

KENP1.PNG

..but I get the following message (whichever level I try to apply these settings to)

kenp2.PNG

Please help!!

Thanks.
 
Hi there,

Make sure you click Database Security on the left side, not Windows Security. Providing that your firewall isn't blocking ports, I believe that should do it for you.
 
Hi there,

Make sure you click Database Security on the left side, not Windows Security. Providing that your firewall isn't blocking ports, I believe that should do it for you.

Thanks, that has helped but I now hit a new problem:

kenp3.PNG

What do I need to do now?
 
A couple of thoughts on this:

  • Do I need to install special software on my laptop, e.g SQL server?
  • Could my McAfee antivirus be getting in the way?
 
I've tried a bit of McAfee configuration - see below - but it has no effect:

Kenp5.PNG

Kenp6.PNG
 
A couple of thoughts on this:

  • Do I need to install special software on my laptop, e.g SQL server?

No, that shouldn't be necessary at all.

Any chance you're behind a corporate firewall? That's usually the number one blocker of these things. Try it from a home network?
 
Thanks Ken.

I'm on a home network, with a McAfee security package providing firewall protection
 
I am on a home network with McAfee and don't have these issues - I would think that something else is at play here. Do you have extra firewall settings on your router?
 
Hi Ali,

I'm going to try this from a different computer today/tomorrow and will see what happens. Will let you know
 
Mr. Ken,

When I had seen Power Query in Excel 2013, I thought this is the end for all my problems related to Date formats. Power Query is definitely solves most of our data transforming problems. One of the most classic problems is date formats. But strangely there is no straight way still after 7 years for changing dates from DD/MM/YYYY to MM/DD/YYYY or any other formats like YYYY/DD/MM, YYYYMM/DD etc. Am I right or am I ignorant of some option available already?

Srinivas
 
Last edited:
One of the most classic problems is date formats. But strangely there is no straight way still after 7 years for changing dates from DD/MM/YYYY to MM/DD/YYYY or any other formats like YYYY/DD/MM, YYYYMM/DD etc.

No, not exactly. The thing is that Power Query doesn't deal with formats, it deals with data types, and Excel deals with formats. What I mean is that Power Query's job is to convert your data from it's provided format (DD/MM/YY, YY/MM/DD, MM/DD/YY, etc..) and convert it to the correct date serial number. That gets placed in the Excel cell, then you format it with whatever format you want to display it in. To do this, you need to use the Using Locale setting on your Change Type options.

The key to this is understanding that this function is typically only applied when you are importing data from a flat file (txt, csv, prn, etc). Once the data is in an Excel cell, the date serial number has already been created, so it's too late to change it.
 
Back
Top