Power Query is an Excel feature available within the newest versions of Excel. It used to be an add-in feature called Power Query. Microsoft calls it Power Query as well as Get and Transform and it is a business intelligence tool that allows you to retrieve data from a variety of sources, clean it and then transform or refine the data as needed. For example, you can import a database file, parse data, split or combine columns, change data types, group data and more. You can then take that transformed data back into Excel and analyze it using your preferred methods. What makes this feature exciting is that Excel records all your steps so the data in the file can automatically be refreshed with Excel retrieving the source data, applying the same steps and updating the file for you. This is a huge time saver and will also reduce errors.

Version: **This feature is only available in Excel versions starting with Excel 2016 To, see if you have this feature, simply click on the Data tab in Excel. You should see a section of the ribbon called Get & Transform. If you do not see this section, then you do not have the Get & Transform Data feature and will not be able to do any of the exercises in this eBook. If you have the older add-in feature called Power Query you should be able to follow along just fine. You can also download an add-in version if you have an earlier Excel version.

Update: This course is based upon Microsoft 365 and updates through November 5, 2024. Depending upon your version, some of the screenshots and commands may differ.

Learning Objectives
  • Recognize when to use the Power Query feature and how it transforms data.
  • Identify the different parts of Power Query.
  • Identify and recognize how to clean and refine data using Power Query.
  • Recognize different data types.
  • Discover and distinguish different methods of Joining tables together.
  • Recognize how to combine and append tables or files.
Last updated/reviewed: November 6, 2024
9 Reviews (33 ratings)

Reviews

5
Anonymous Author
This is my first time taking a text based course, but this is a very helpful and informative course. Text based content walked me through the major tips of using power query functions in my own pace. This is really great method to learn a hands-on tool. But the only drawback might be that the last exercise in the Unpivot section was not very clear. My data could not be successfully loaded to Excel for some reason that the text did not mention even though I followed the same steps. I spent a whole afternoon working on this course, which is more than the expected 2 hours.

5
Anonymous Author
This is an informative course. It helps lay out the Power Query steps easier and explains the logic behind certain steps. I thought the questions about joins seemed to be switched (right/left) for the answers. Not sure - perhaps the wording is just strange.

5
Anonymous Author
I've taken a few Power Query webinars, but had trouble doing it on my own. This really helped to be able to reference the text. It was easy to follow step by step and doing it at the same time while reading really helps.

4
Anonymous Author
The information was set out very well. There were lots of examples and practice exercises. The review questions and helped solidify the information. This was great information and very practical for using in projects.

5
Anonymous Author
The PDF with this course included clear step-by-step instructions with screen shots. Having practice data was very useful as well.

5
Member's Profile
I have some basic understanding of the Power Query process in Excel, but I learned several new items in this.

5
Anonymous Author
Extremely helpful and now I can handle power query issues as I suffered for this more than 5 years

5
Member's Profile
Meticulously prepared course, extremely beneficial to power query users!

4
Anonymous Author
Best Powe BI course! Easy to follow and understand. Thank you!

Prerequisites
Course Complexity: Intermediate

Intermediate to Advanced Knowledge in Excel.
An understanding of how databases work would be helpful but not necessary.

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
Course Syllabus
Introduction And Overview
  Power Query in Excel: Get & Transform DataPDF
Course Material
  Workbook: List Cities Us 30JCSV
  Workbook: 20190201_SnowRegions.CSV
  Workbook: NFL2018CSV
  Workbook: Charts of AccountsXLSX
  Workbook: Product ShipmentsXLSX
  Workbook: Regional Sales InfoXLSX
  Workbook: 3 Table PivotXLSX
  Workbook: Join ExamplesXLSX
  Exercise and Answers FilesXLSX
Review And Test
  REVIEW QUESTIONSquiz
 FINAL EXAMexam