• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Introduction to Power Query

You are here: Home / Power Query / Introduction to Power Query
power query
October 15, 2020 by Mynda Treacy

What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing it is. Stories of automating tasks that used to take 3 hours now taking 3 minutes is not uncommon or an exaggeration.

If you haven’t heard of Excel’s Power Query tool, or you’ve heard of it but you’re not sure if it’ll be useful to you, then check out the video below where I showcase what the fuss is all about.

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook & data files and follow along. Note: This is a .zip file please ensure your browser doesn't change the file extension on download.

Where is Power Query

Power Query is a tool built by Microsoft for Excel and Power BI. It’s the same tool in both apps, so you only need to learn it once and you can use it in either app.

In Excel the tools are in different places depending on the version of Excel you have:

Power Query in Excel 2016 onward

Power Query in Excel 2010 and 2013

Excel 2010 and 2013 users download Power Query here.

Power Query in Power BI Desktop

Click here to find Power Query in your version of Excel.

Purpose

Power Query's purpose is to:

automate Automate the laborious getting and cleaning data tasks.
no coding Eliminate the need to be a programmer: traditionally if you wanted to automate these laborious tasks, you’d use Excel’s programming language, VBA. However, Power Query doesn’t require programming knowledge as most of its tools are available from the GUI with point and click ease, as you can see in the video.
save time Reduce time-consuming tasks that can take hours, down to a fraction of the time, sometimes as little as a few seconds.
 

Once you’ve gathered and cleaned your data using Power Query, you’re ready to use tools like formulas, PivotTables, and Power Pivot to analyse and visualise the data.

Data Sources

Power Query can get data from almost any file source imaginable, including proprietary systems that have either OLEDB or ODBC drivers. The image below lists the connectors currently available in Excel, but they're adding more all the time.

data sources

Power BI has even more sources available, including loads of online services. See the full list here.

Built-in Data Cleaning Tools

There is a vast range of built-in data cleaning and transformation tools readily available from Power Query's ribbon. Below are screenshots of the main tabs, but the menus don’t do its power justice. If you haven’t seen the video above, take a few minutes to see how easy it is to use.

Power Query home tab

Power Query transform tab

Power Query add column tab

For those keen to take things to the next level, there is also an extensive function library.

Learn More

If you’re keen to get up to speed quickly, please consider my comprehensive Power Query course.

power query
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Power Query Posts

get started with power query

Get Started with Power Query

10x Productivity with Excel Power Query in 3 easy steps. Get data > Transform Data > Load Data = HOURS Saved!

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.


Category: Power Query
Previous Post:grouped running totals in power queryGrouped Running Totals in Power Query
Next Post:Searching for Text Strings in Power QuerySearching for Text Strings in Power Query

Reader Interactions

Comments

  1. Sean

    February 24, 2023 at 1:17 am

    When loading the files from the folder. Power Query didn’t combine and transform the data when I clicked combine and transform data. It only pulls data from 1 sheet of 1 workbook. Negates any attempt at what I wanted to accomplish. Will have to stick with the macros I’ve created for now.

    Reply
    • Mynda Treacy

      February 24, 2023 at 9:26 am

      Hi Sean,

      Sounds like there’s something wrong with the query preventing the files being combined. You’re welcome to post your question on our Excel forum where you can also upload screenshots/files and we can help you further.

      Mynda

      Reply
  2. John D

    February 4, 2023 at 4:10 am

    Madam happy to have the chance to visit your site and thank you for all you do, empowering too many people all over the globe with life changing skills. Your “school” on youtube has transformed many lives better than power query itself. It is an honour.

    Reply
    • Mynda Treacy

      February 4, 2023 at 9:25 am

      That’s so wonderful to hear, John! Thank you 🙂

      Reply
  3. Waris

    October 3, 2022 at 8:33 pm

    I find this so helpful. Thank you so much

    Reply
    • Philip Treacy

      October 4, 2022 at 8:48 am

      You’re welcome.

      Reply
  4. Will

    August 20, 2021 at 12:44 pm

    Hi Mynda and Phil – some guidance on achieving small file sizes –

    I know each pivot table even with “Save source data with file” unticked increases the file size. With PQ, I build a pivot table by using an external data source -> choose connection to connect to a query. If I the tick add this data to the Data Model, the file size increases.

    What in your opinion is the best way to achieve small file sizes –
    – multiple pivot tables using connections; OR
    – multiple pivots connecting to a Data Model loaded once; OR
    – building the queries in PQ and using a Pivot Table only for summarising

    Keeping in mind the time taken to load a large queries. I try and use referencing to queries rather than duplicating them or buildling new ones.

    Thanks and regards

    Reply
    • Will

      August 20, 2021 at 3:47 pm

      Hi all – I am responding to my own comment

      – multiple pivots using connections works wonders. My file size is in KB.

      Build queries in PQ and connect the final query to a pivot table which you can replicate to obtain various outputs.

      Mynda, please advise it this approach is incorrect

      Reply
      • Mynda Treacy

        August 20, 2021 at 9:28 pm

        Hi Will,

        Whether you use PQ to get the data and load it to a PivotTable with connection only or load it to the data model and then build PivotTables should, most of the time result in a smaller file when you choose the data model route. With both scenarios the data is loaded to the Excel file: either in the Pivot cache or the data model. The data model has more sophisticated compression algorithms, but their effectiveness depends on the type of data you have. The more duplicate records in each column the better it can compress the data.

        So, it will come down to trial and error as to which is most effective for you.

        Mynda

        Reply
        • Will

          August 23, 2021 at 5:26 pm

          Thank you Mynda for the advise.

          Unfortunately, the size of the file ballooned to 8.7MB (2 pivot tables – Query: connection only added to data model) from 234KB (15 pivot tables – Query: connection only Not added to data model).

          Maybe I am doing something incorrectly. I’ll keep trying.

          Reply
          • Mynda Treacy

            August 23, 2021 at 6:16 pm

            Not necessarily. The Power Pivot compression algorithm is best when there is a lot of duplicate data. Sounds like your data is better suited to the regular pivot cache.

  5. Richard Dickson

    May 7, 2021 at 12:24 am

    Hello I am trying to find your CSV files for the youtube coarse, but haven’t been able to locate them. Can you give me a link.

    Reply
    • Mynda Treacy

      May 7, 2021 at 8:45 am

      Hi Richard, the files are available to download from this page under the video. You need to enter your email address to reveal the download link. Mynda

      Reply
  6. Mario

    November 1, 2020 at 12:00 pm

    Hi Mynda.

    I search I course in Power Query to introduce more intermediate or advanced concept, I bought book from Ken Puls and I think was a great book.

    In your Power Query course, do you teach me some intermediate or advanced concepts?

    Thanks

    Reply
    • Mynda Treacy

      November 1, 2020 at 1:42 pm

      Hi Mario, thanks for your interest in my course. Yes, you can see the full syllabus on the Power Query Course page. Please get in touch if you have any further questions. Mynda

      Reply
  7. Unmesh

    October 21, 2020 at 10:12 pm

    Hi Mynda,

    After closing and load the query, Am getting an expression error which says The Key didn’t match any rows in the table.

    Can you please help me to correct this error. Thanking you in advance for this help.

    Reply
    • Mynda Treacy

      October 22, 2020 at 8:43 am

      Hi Unmesh, this means you had column names in your original source file that have since changed. Either update the column references in the M code of the query via the advanced editor to match your current source file, or change the source file to match the original and expected names. Mynda

      Reply
  8. John Brewster

    October 17, 2020 at 6:51 pm

    On your video, “Power Query Automates Boring Tasks”, I follow the instructions up to the point where I copy the address and Paste it in the Browse dialogue box, I press Ok and then I import the CSV files also and not the 3 Sales files in the video so could you please, please tell me what I am doing wrong.

    Reply
    • Mynda Treacy

      October 18, 2020 at 10:47 am

      Hi John,

      It’s difficult to say without seeing some screenshots of what you see at the screen where you click the ‘Transform’ button. Please post your question on our Excel forum where you can also upload a sample file and some screenshots and we can help you further.

      Mynda

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x