What is Power Query?

So what is Power Query?

You do work with Excel, right? Did you know that there is an amazing new piece of Excel that allows you to do magic in your data, avoid repetitive manual work and save you time and money?

Why is it relevant for you?

  • Every time you copy/paste the same type of data to your workbook, and manually clean it, you are probably wasting precious time, unaware of the potential in front of you.
  • Every time you rely on others to get your data in the right shape and condition, you should know that there is an easier way.
  • Every time you need to make quick informed decisions, but feel obstacles in your way to insights, you should remind yourself that these moments can change from maddening frustration to shear excitement and fun.

Are you ready for the change?

Power Query is an awesome import and transformation technology which is offered as part of Microsoft Excel and Power BI. It empowers any person with basic Excel skills to perform amazing data import, reshape and cleansing, with simple UI clicks and an awesome experience.

Power Query can save you unprecedented time, as you import a preview of your data, then define multiple transformation steps, and load the data to your Excel workbook, or Power BI Desktop file. In addition, when your data changes (e.g. an new file is added to the folder or new records are added to your database), a single click on Refresh All button will get and transform your data as you had initially defined.  You can also go back and edit the transformation steps, if necessary.

Power Query supports a wide variety of external data sources in Excel here and Power BI here.

You may find different (and unfortunately confusing) terms and UI entry points that lead to the same technology and experiences:

  • Power Query Add-In is available in Excel 2010 and Excel 2013. After you install it from here, you will find a separate Power Query tab in Excel.
  • Get & Transform – This is the new section in the Data tab of Excel 2016. You can find all of Power Query functionality there.
Get & Transform
Look for Power Query technology in Excel 2016’s Data tab
  • Inside Power BI Desktop you can start new queries or manage existing ones through External Data section.

Entry point for Power Query in Power BI Desktop

  • Data Explorer is the first name that was provided by Microsoft to this technology. If you find an old resource that use the term Data Explorer, you should consider stop reading it as it might be outdated.

How can I start?

  • Do you have Excel 2010 or 2013? You can download Power Query Add-In here.
  • Already using Excel 2016? You are ready to go. All of Power Query functionality is available in the Data tab Get & Transform section.
  • Would you like to start your import and data transformation outside of Excel? Download Power BI Desktop here (Power BI Desktop is the authoring and publishing tool of Microsoft Power BI service here. It shares Power Query and other fantastic technologies like Power Pivot with Excel).

Where can I learn about Power Query?

  • If you use Excel 2016, start with Microsoft official site here.
  • If you have Excel 2010 or 2013, you can start here.
  • There is a great Microsoft/Edx training that covers all the basics here.
  • There are fantastic blogs as well. Here are few of the leading bloggers. If you are new to Power Query, you must check out their sites (If I missed you, please share below):
  • And there is also my blog DataChant.com, where you can learn the magic of Power Query, and follow my tutorials on your way to become a true wizard.

Why should I follow DataChant.com?

  • Honestly? Because you reach this line 🙂
  • But now seriously, as a former Senior Program Manager in Excel team, I had the pleasure of working with the top of Excel and Power BI experts and learn from the best. I was fortunate to lead the integration of Power Query in Excel 2016 (You can learn more about the integration in my Office Blog post here) and published dozens of tutorials that range from basic to advanced scenarios.
  • To learn more about DataChant go here.

Are you familiar with useful resources about Power Query? Contact me at [email protected], and let me know, or share it in the comments below.

Leave a Reply