Power Query

  • Connecting to APIs with Power BI (Power Query)–Part 1 of 2


    Why is it so complex to connect to an API with Power BI or Power Query? The OAuth explained

    It’s easier to explain using analogies, so let’s go with that!

    Analogy: Imagine that you have a gym subscription and this gym also has a VIP lounge, but to get to the lounge you need to head to the front-desk to get authorization for a limited time.

    Using MailChimp as an example:

    • The gym is actually MailChimp – they put all the facilities for you
    • The lounge section is their API and its where you can “access” your data
    • The front-desk authorization is what we know as a Authorization Token, which grants us the chance to “be” in the API

    I’m overly simplifying things here, but the main idea is that the “vip lounge” is the place that the “gym” gives you to do what they give you access to do. They (the gym) could give you access to read, write, delete or modify “data”.

  • 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 Sonrisa

    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:




  • New Functionality in Power BI Desktop–January 2017


    Almost every month the Power BI team delivers a new version of the Power BI Desktop with some new connector, new ways to visualize data and some other ways to work even easier with your data model or your ‘Get Data’ experience.

    The one that I loved this time and that might not be completely visible for most people is the new extract values from a nested list. The whole reason why this might not be visible to most users is that you almost never see a List when working with just the UI of Power Query. So, how do we even get to use this new feature?

    Let’s try with a simple example:


    A table with stats on some marathon races with fields like, Marathon ID, the Name of the participant and the place or rank that the participant got on each marathon

    We would like to work our way from the table on the left to the one on the right.

  • New eBook about Power Query: Secure your FREE copy now!


    Data Wizard cover

    YES! – We’ve been working hard on this one and it’ll be a completely free eBook. If you want to get your copy, be sure to sign up to our newsletter here and you’ll be one of the firsts to receive a free copy of this ebook.

  • 3 New Power Query Patterns & Upcoming Power Query Workshop


    We’ve posted 3 new Patterns for you to look at in the Power Query Training site. These are:

    In case you’re late to the party, we have also released the new date for our upcoming Power Query Workshop. Check the details below.

    • 3

    • Workshop Summary

      • 2 Days of Content
      • 1 Q&A Session
      • 1 FREE eBook for each participant
      • 6 Assignments
      • Full HD Recordings in MP4 format

      Start Date: August 12, 2015 @ 10am UTC-0500h 

      Course Information
  • ABC Analysis with Power Query


    After an amazing first Power Query workshop in English, we are providing our attendees with even more CRAZY cool content that they’ll be able to put into practice (and understand) right away!

    You might have already seen an ABC Classification Pattern with Power Pivot here, but this time you’re going to see this pattern being implemented with nothing but M code in Power Query.

    Why? you might ask.

    Is not only a simple and quick solution, in the case that you don’t want to use Power Pivot, but it also has some major feature advantages that can make truly unique solutions that could take you more time to implement in Power Pivot or they might end up adding unnecessary complexity to an already complex Data Model.

    Let’s step into the solution and find out why this is a major step forward all Excel Users and even long-time Power Pivot users.

  • Power Query for Excel – Replace values using values from another column

    Another video trick for Power Query. A glimpse of how advanced you can go with Power Query using custom M code.

    Be sure to check out more tips and tricks like this one on the oficial Power Query Training page

  • Getting Started & Understanding Power Query and the M Language


    Let’s put Power Query and the M Language under the scope

    Power Query is a breakthrough tool for self-service Business Intelligence and it’s the new best tool released by Microsoft within the span of 5 years. I’ve already posted about what Power Query is and what it represent to us but, How can I get started with Power Query and the M Language?

    Well, this post will try to tackle that question and give you a sense of how to start using Power Query and understand its language.

    I’ve divided this into 2 main sections that I truly believe should be first primary focus of our journey when we try to learn Power Query and the M language. These are:

    1. The Power Query & M mechanism or Workflow
    2. The actual M Language itself

    Let’s get started with the basics!

  • Combine Multiple excel, csv and txt files – New guest post on PowerPivotPro.com


    (Click on the image above to read the post)

    Have you ever wanted to Combine multiple:

    • Excel Files
      • Spreadsheets
      • Tables
      • Defined Names
    • CSV files
    • TXT files

    Find out the most optimal way to do so by reading my guest post on powerpivotpro.com

  • What is Power Query?

     Power Query unofficial logo(Power Query unofficial logo)

    A long overdue post!

    It’s a bit crazy that at this point I haven’t posted anything clearly defining what Power Query is and where it fits in the whole self-service BI experience with Power BI.

    So it’s time to actually sum up some points and post them here so we can clear that doubt about what really is Power Query and why you SHOULD care about it.

    What exactly is Power Query?