Learn POWER QUERY in < 10 Minutes
No Non-Sense Practical Guide to Getting Started with Power Query.
Last Update: 08 May' 2026
In the last 10 years, if there’s one Excel skill that has given me the best ROI, it would be Power Query. I still remember using Power Query for the first time just to combine data from a few Excel files, and since then, it has probably saved me thousands of hours.
What is Power Query?
Power Query is a Data Transformation tool. With Power Query, you can connect a data source, and then transform it with a few click, and load that data to your Excel workbook. Once you perform something it stays like an automation. Next time, you can referesh that query and it will do all (Source Transformation Load) by itself. In technical language, Power Query is Microsoft’s ETL, that means the same as I have mentioned, Extract, Transform, & Load.
Extract
Pull raw data from source systems
Transform
Clean, format, and enrich the data
Load
Move data into target destination
Power Query Can be used to do simple as well as complex tasks of data transformation. From loading a simple table and transforming it to getting data from Google Drive.
Where is Power Query in Excel?
In Excel, Power Query is available on the Data tab. Once you click the Data tab, you’ll find a group of options called Get & Transform, where all the Power Query options are listed. If you don’t see the Power Query options there, you should check which version of Excel you are using and whether Power Query is available in that version.
Excel Version | Power Query Availability | Add-in Required? | Mac Support |
|---|---|---|---|
Microsoft 365 (Windows) | Built-in (Data → Get & Transform) | No | Yes — available in Microsoft 365 for Mac (v16.69 or later) |
Excel 2024 | Built-in (Data → Get & Transform) | No | Yes — Microsoft 365 subscription required on Mac |
Excel 2021 | Built-in (Data → Get & Transform) | No | Not available in Office LTSC 2021 for Mac (perpetual) |
Excel 2019 | Built-in (Data → Get & Transform) | No | Not available on Mac |
Excel 2016 | Built-in (Data → Get & Transform) | No | Not available on Mac |
Excel 2013 | Available as a free add-in | Yes — download from Microsoft | Not available on Mac |
Excel 2010 | Available as a free add-in (Professional Plus only) | Yes — download from Microsoft | Not available on Mac |
Excel 2007 and earlier | Not supported | — | Not available on Mac |
Open Power Query Editor
Power Query has its own editor, a separate window where you load data, build transformation steps, and see the result before sending it back to Excel. To open it from Excel, go to the Data tab and click Get Data → Launch Power Query Editor.
The middle of the screen shows a preview of your data. Every change you make appears here instantly. On the right, the Applied Steps pane records each transformation in order. At the top, the ribbon has tabs that look like Excel, Home for common tasks, Transform for advanced reshaping, Add Column for new calculated columns, and View for editor settings.
Time Require: 3 Minutes
How to use Power Query
Now let's create your first query in power query to transform data. And for this, we are going to follow our three steps framework where we load our data first and then we transform it and then we're gonna load the transform data back to the new worksheet.
Step - 1 Load Data into Power Query Editor
To start with Power Query, the first step is to load your data into the Power Query Editor. To do that, you usually need to convert your data range into an Excel table first. If your data is not already converted into an Excel table and you try to load it into Power Query, Excel will first ask you to create a table, and then it will load that table into the Power Query Editor.
In one way or another, your data needs to be converted into an Excel table before Power Query can load it into the editor for transformation. See the following table in my worksheet, where I have five different columns. I want to perform a few transformations on this table, so let’s load it into the Power Query Editor.
In one way or another, your data needs to be converted into an Excel table before Power Query can load it into the editor for transformation. See the following table in my worksheet, where I have five different columns. I want to perform a few transformations on this table, so let’s load it into the Power Query Editor.
To load this table, I’ll select any cell inside the table, go to the Data tab, click the Get Data drop-down, then go to From Other Sources, and select From Table/Range.
When you click the From Table/Range option, Excel instantly asks you to convert your data into an Excel table. Once you click OK, it converts the data into an Excel table and loads it into the Power Query Editor within a few seconds.
Now, at this point, we are inside the Power Query Editor. In the next step, we’re going to transform our data.
Step - 2: Transform and Clean Data
Now it’s time to transform our data. Once the data is loaded into the Power Query Editor, you’ll see all the transformation options available on the ribbon.
Remove Duplicates
For this data, the first transformation we’re going to perform is to remove duplicate values.
For this, go to the Home tab in the Power Query Editor. In the Home tab, you’ll find a drop-down called Remove Rows. When you click this drop-down, you’ll see the option Remove Duplicates.
The moment you click Remove Duplicates, Power Query removes all the duplicate rows from the data. You’ll also see a new step added in the Applied Steps pane called Removed Duplicates.
Split Column
The next step is to split a column. If you look at the second column, Product Info, it contains both the product name and the product code. I want to separate these into two different columns: one for the product name and one for the product code.
To do this, I’ll right-click on the column header, go to Split Column, and then select By Delimiter.
In the Split Column dialog box, Power Query is smart enough to identify the delimiter I have, which is a hyphen, and it already shows it in the input bar. Now I just need to click OK, and it will split that column using the delimiter into two different columns.
Click OK, and the column is now split into two different columns.
Rename Columns
Now we have two different columns, but the names of the columns still need to be corrected.
I need to double-click on the column header, and it will allow you to edit the name of the column. You can see in the screenshot below that the first column is added, and now I can rename it to Product Name.
I can do the same thing with the second column, where I have the product groups.
Remove Time from Dates
In the Order Date Time column, I have both date and time values. Here, I don’t need the time, I only want the date. But unlike Excel, here I don’t need to write a formula. I have a direct option that I can use to remove the time in just a few clicks.
To use this option, you just need to right-click on the column header, and there you will find an option called Transform. From Transform, click on Date Only.
Trim Customer Names
Now, in the last transformation, what we need to do is trim the Customer Name. If you look at the Customer Name column, we have a few leading and trailing spaces with every name in the column, and these are the spaces that we need to remove.
Again, the option is straightforward. You just need to right-click on the column header, then go to Transform, and within Transform, you will find the option called Trim. When you click on Trim, it removes these leading and trailing spaces.
And finally, our data is now transformed, and we have completed five different transformations with Power Query. You can see that these transformations did not even take 2 minutes to perform if you compare this with how we do it in Excel using traditional methods.
Step - 3: Load Data into the Excel Worksheet
Now, at this point, our data is ready, and we need to load it from the Power Query Editor back into our worksheet.
To do this, I’ll go to the Home tab, where we have the Close & Load option. There is also a drop-down for Close & Load, but for now, I’m going to click the main Close & Load button.
The moment I click Close & Load, Power Query loads the transformed data back into the workbook as a new worksheet.
