Power Query Recipes Now Available

As you may have heard, we have been working on a set of helpful Power Query Recipes for Excel and Power BI and are excited to announce that they are now officially available!

Are our Power Query Recipes for you?

The Power Query Recipes are targeted at people who are familiar with the Power Query interface (in either Excel or Power BI), and will lead you step-by-step through the process needed to clean up and convert your data from one format into another.  And if you're not already comfortable with Power Query?  Consider joining the Power Query Academy so we can change that!

So what is in the Power Query Recipes package?

There are currently over 30 cards in the set, showing easy-to-follow steps that will deal with a variety of common data issues.  I personally am finding them super useful, and often refer back to them when I'm helping people clean up their data in person or in forums.

We've even marked each of the Power Query Recipes that has a video version in Power Query Academy.  If you are already subscribed to our Academy, simply click the video camera in the bottom corner, and it will take you straight to the appropriate video so that you can see the technique demonstrated in a live setting.

Are there samples of the Power Query Recipes?

Of course there are!

Here's an example of one of my favorites, which lays out how to create Full Anti Join, something that does not exist in the regular Power Query user interface:

image

And another which shows how to create a Calendar Table on the fly:

image

We actually have a sample package available which includes four recipe cards (including the precedent card for the calendar recipe shown above), as well as the full table of contents for the current version.

Just some of the patterns included in the full version are:

  • Splitting data into new columns or rows
  • Pivoting, unpivoting, and transposing data
  • Several different ways to merge tables (including the Full Anti Join shown above)
  • Extracting a new column based on values in the prior or next rows
  • Six different ranking methods
  • Creating dynamic calendar tables
  • Adding a random number to all rows

Will there be more Power Query Recipes?

Oh yes!  While there are already over 30 recipes in the set, we already have another 20 on our "to-do" list, and are adding to that list regularly.  Our intention is to release new recipes on a regular basis, putting even more Power Query resources at your fingertips.

How do I get my copy of the Power Query Recipes?

There are a few different options:

  1. If you are a Power Query Academy member*, the recipes are already included in your registration.
  1. Pre-order a copy of the new 2nd edition of our M is for Data Monkey book from Amazon by September 30, 2018 and receive a FREE set of the initial  recipes.**
  1. Purchase the current stand-alone set of recipes from the Power Query Training web store for $24.95 USD. This is also an option to add a subscription ($2.95 USD every 3 months) so that you get all the new recipe cards as we expand the set.

* Please note that the recipe cards are not included in the free Power Query Academy trial, only with the full course.
**Upon receipt of your Amazon proof of purchase being received via email at support@powerquery.training, we will provide a coupon code for $24.95 which can be applied to either the stand-alone or subscription purchase.  Act fast though, as this offer ends on October 31, 2018.

9 thoughts on “Power Query Recipes Now Available

  1. Hi Prashant, you should have got an email letting you know, but if you didn't... just log in to the Academy. The cards have been added to Module 0 and will be kept updated there as we add more.

    Cheers!

  2. Ken, 2 questions:
    1) Amazon shows "Master Your Data with Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow" releasing March 1, 2019. I assume that is the 2nd edition?
    2) Sending you my receipt for that will get me the FULL set of recipes? I wasn't 100% clear on the above language - "initial recipes" then a coupon...

  3. Hi Ed, that’s the correct book, yes.

    With regards to the recipes, we will provide a coupon for 100% off the “current version” price which has 31 recipes in it. We will be adding more recipes later, but you’d have to opt in to the subscription in order to receive the new (or updated) recipes as well.

  4. I have the 1st edition of Mi4DM. What's new in the 2nd edition (other than the title and the cover art ;-))?

  5. Hi @Jim. The things I know of today: Conditional columns, columns from examples, 7 different join types, data quality previews, approximate match lookups, Cartesian products, grouping...

    And that's what I know of today. There are other features that are being developed and released constantly, and if they make the product in time, we'll try to include those too. (It's MUCH bigger than just the cover art!)

  6. hi
    how do you format Time ( hh:mm:ss ) to have an apostrophe before all the time
    eg :- 20:25:00 - '20:25:00
    14:04:00 - '14:04:00
    15:40:00 - '15:40:00

    the are too many of the time and can not do it manually, looking for the formula

  7. You could write a custom formula like Time.ToText("'" & [Time column]), but honestly, you shouldn't. You should format it as a time, then land it to Excel or Power BI, and format it there. (Power Query is about data types, not formatting.)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.