Yes, you heard it right.
POWER QUERY is one of those things which can be a game changer for you.
And today in this post, I’m gonna share with you some of the most amazing Power Query tips and tricks which you can start using from today, right now.
These tips will not only help you to save your time but also going to inspire you to use POWER QUERY for managing data.
And I’m sure after finishing this post you’ll thank me.
Important Note: Make sure to bookmark this page so that you refer to it in future.
…now, without any further ado, let’s check what are these tips and how they can help you.
But Why Should I Use Power Query?
I want to share some of the strong reasons with you and want you to go ahead learn these tips and tricks about power query.
#1. To Transform Your Data in an Easiest Way
One of the main reasons to use power query is that you transform your data easily.
Normally, you use formulas and pivot tables but with power query, all the major data formation tasks can be done in no time.
#2. Power Query is Real Time
This is the second big reason that power query is real time, it’s like a one-time setup.
Write a query for once and you can refresh it every time there is a change in data or you can also define an auto refresh time (Tip #26).
#3. You Just Need Few Clicks
As I said, normally you use formulas and pivot tables for data transformations but with POWER QUERY you can do a lot thing just with clicks.
There is no need to write formulas or codes.
How to Install Power Query - Steps
Before you start using these tricks you must have Power Query installed in your Excel.
And if you are one of those Excel users who don’t have power query Add-In, you use these steps to install it.
For Excel 2016 or Office 365:
If you are using Excel 365 or Excel 2016 version it’s already there at Data tab as “Get & Transform Data”.
For 2013 and 2010 versions here are the steps:
- First of all, download the add-in from here (Microsoft’s Official Website).
- Once you have downloaded the file, open it and follow the instructions.
- After that, you’ll automatically get the “Power Query” tab on your Excel ribbon.
If somehow that “POWER QUERY” tab doesn’t appear, there no need to worry about it.
You can add it using COM Add-ins option.
- Go to File Tab ➜ Options ➜ Add-ins.
- In “Add-In” options, select “COM Add-ins” and click GO.
- After that, tick mark “Microsoft Power Query for Excel”.
- In the end, click OK.
BOOM! now you have a new tab on your ribbon with the name of “Power Query”.
Open Power Query Editor and Load Data into it
You have different ways to add data into power query editor. Well, if you have data in your worksheet you can choose to insert it from there.
- Go to Data Tab ➜ Get & Transform Data ➜ From Table/Range.
- Click OK to convert that range into an Excel table.
- And instantly after that, you’ll get that table in power query editor, just like below.
Top 25 Power Query Tips and Tricks To Save Ton of Time in Everyday Work
Now it’s time to learn all these power query tips…
- Replace Values
- Sorting - Ascending and Descending
- Remove Columns
- Split Column
- Rename a Column
- Duplicate Column
- Merge Column
- Transpose Column or Row
- Replace/Remove Errors
- Change Data Type
- Add Column from Examples
- Unpivot Table
- Change Case
- TRIM and Clean
- Add Prefix/Suffix
- Extract Values
- Only Date or Time
- Combine Date and Time
- Rounding Numbers
- Group by
- Remove Negative Values
- Add a Custom Column
- Auto Refresh a Query
- Pivot Table from Multiple Workbooks
…so let’s get started.
1. Replace Values
Alright, just think like this:
You have a list with some values and you want to replace a particular value or some values with something else.
Well, with the help of power query you can create a query and replace that particular values, in no time.
In the below list, I want to replace my name “Puneet” with “Punit”.
- First of all, edit the list in the power query editor.
- After that, in power query editor, go to “Transform Tab” and click “Replace Values”.
- Now, in “Value To Find”, enter “Puneet” and in “Replace With” enter “Punit” and after that, click OK.
- Once you click OK all the values replace with the new values and now, click on “Close and Load” to load data in the worksheet.
But here’s the best part:
The query you have just created is a real time.
When you refresh the query again it will replace all the newly entered values.
2. Sorting - Ascending and Descending
Just like normal sorting, you can sort data by using power query.
I’m using the same name list which we have used in the above and here are the steps.
- First of all, load data in the power query editor.
- In power query editor, you have two sorting buttons (Ascending and Descending).
- Click on any of these buttons to sort.
- In the end, click on “Close and Load” to load data in the worksheet.
You might be wondering:
Why should I use power query if I can use normal sorting in a worksheet?
As I said, power query is a real-time thing.
You can create an auto-refresh query (Tip #26) which will refresh after a particular time and sort your data automatically.
3. Remove Columns
A lot of time it happens:
You get data from somewhere and you need to delete some columns from that data.
The thing is, you need to delete those columns everytime you add new data.
But with power query, you can create a query.
Here are the steps:
- First of all, open data in the power query editor.
- After that, select the column or multiple columns which you want to select.
- Now, right click and select “Remove”.
- In the end, click on “Close and Load” to load data.
Quick Tip: There’s also an option to “Remove Other Columns” where you can delete all the unselected columns.
4. Split Column
- First of all, edit it in the power query editor.
- After that, select the column and go to the Home Tab ➜ Transform ➜ Split Column ➜ By Delimiter.
- Select the custom from the drop down and enter “-” into it.
- Now, here you have three different options to split a column.
- Left-most Delimiter
- Right-most Delimiter
- Each occurrence of the delimiter
As we have only on delimiter in a cell, all three will work in the same way, but if you have more than one delimiter then you can choose accordingly.
- In the end, click OK and click on “Close and Load” to load data.
5. Rename a Column
You can simply rename a column by right click and then click on “Rename”.
Quick Tip: Let say you have a query for renaming a column and someone else rename it by mistake. You can restore that name just with a click.
6. Duplicate Column
In Power Query, there is a simple option to create a duplicate column.
All you need to do is right click on the column for which you want to create a duplicate column and then click on “Duplicate Column”.
Yes, that’s it.
7. Merge Column
Normally to merge columns and cells we use formulas in Excel but it can be a whole lot easier with power query.
Remember that the full name list (download from here) we have split (Tip #4). Now, let’s merge it using a space.
Follow these steps:
- Once you add data into the power query editor, select both of the columns.
- After that, right click on it and click on “Merge Columns”.
- Now in merge columns window, select the separator from the drop down (here we are using a space) and add a name for the new merged column.
- In click OK and load data into the worksheet.
Quick Tip: You can also use a custom separator for merging two columns.
8. Transpose Column or Row
In power query, transposing is a cup of cake.
Yes, just one click.
- Once you load data into power query editor, you just need to select the column(s) or row(s).
- Go to Transform Tab ➜ Table ➜ Transpose.
9. Replace/Remove Errors
This is the real thing.
Normally, for replacing or removing errors in Excel you can use find and replace option or VBA code.
But, in power query, it’s a whole lot easier.
Look at the below column of amount where you have some errors and you can use remove them as well as replace them.
When you right click on the column you'll have two options to deal with it
- Replace Errors
- Remove Errors
10. Change Data Type
It’s a common thing:
You have data in a column but it’s not in the right format. So every time you need to change its format.
In the below example, we have date column but these are just serial numbers.
To convert it to date you can use power query and it’s super easy.
- First of all, edit data into power query editor.
- After that, select the column and go to Transform Tab.
- Now, from data type select the “Date” as a type.
Quick Tip: In most of the cases power query detected data type automatically but if it doesn’t you can change it, like the above example.
11. Add Column from Examples
This is something different but quite useful in some specific situations.
Here’s the thing:
In power query, there is an option to add a sample column which is not actually a sample related to the current column.
Let me give you an example:
We have converted serial numbers into dates in the above example and now let’s say you need to add a column where you need to have day name for those dates.
Instead of using a formula or any other option in power query you can use “Add Column from Examples” option.
Here’s how to do this:
- Once you edit your data into power query editor select the column.
- After that, right click on it and click on “Add Column from Examples”.
- Here you’ll get a blank column. Click on the first cell of the column to get the list of values you can insert.
- Select “Day of Week Name from Date” and click OK.
Boom! your new column is here.
12. Unpivot Table
I’ve written a complete step by step guide to unpivot data using power query.
Go check it out.
13. Change Case
You can do by right click on a column and select any of the above three options. Or, go to the Transform Tab ➜ Text Column ➜ Format.
14. TRIM and Clean
To clear data or delete unwanted spaces you can use TRIM and CLEAN option in power query.
Steps are simple:
- Right click on a column or select all the columns if you have multiple columns if you more than one column.
- Go to transform option and select any of the options:
- TRIM: To remove trailing and leading whitespaces from a cell.
- CLEAN: To remove non-printable characters from a cell.
As you can see both of these functions work differently.
15. Add Prefix/Suffix
So you have a list of values and in this list, you want to add a Prefix/Suffix in each cell.
In Excel, you can use the concatenate method but in power query, there is simple to use option for both of these.
- First of all, select the column where you need to add Prefix/Suffix.
- Then, go to the Transform Tab ➜ Text Column ➜ Format ➜ Add Prefix/ Add Suffix.
- Once you click on one of the options you’ll get a dialog box to enter text.
- And after entering text, click OK.
It works same for numbers, text and, dates.
16. Extract Values
If you are a formula savvy, then I’m sure you agree with me that extracting text or number from a cell requires to combine different functions.
Power Query has solved a lot of these things in a good way. You have seven ways to extract values from a cell.
And yes, with a single click.
Just look at the options you have.
Well, it has solved all the basic things we need to extract values.
17. Only Date or Time
This is a game changer, I’m telling you.
It happens a lot of time that you have date and time, both in a single cell but you only need one of them.
Type “Yes” in the comment section if this is a common thing with you.
In power query, you just need a single click, here are the steps.
- Select the column where you have date and time combined.
- If you want Date: Right Click ➜ Transform ➜ Date Only, or, if you want Time: Right Click ➜ Transform ➜ Time Only.
Yes, that’s it.
18. Combine Date and Time
Now you know separate date and time.
So next you need to know how to combine them.
It’s pretty simple.
- First of all, load your data into the power query editor.
- And then select date column and click on the date only option (Tip #17)
- After that select both of the column (Date and Time) and go to transform tab.
- Now, from “Date and Time Column” Group go to Date and click “Combine Date and Time”.
Now you have a new single column with data time combined.
19. Rounding Numbers
We have functions to round numbers in Excel but, so do power query.
And you can do it with click here.
Here are the following options
- Round Down: To round down a number.
- Round Up: To round up a number.
- Round: You can choose up to how my decimals you can round.
Here are the steps:
- First of all, open your data into the power query editor.
- Select the column right click ➜ Transform ➜ Round and select any of the three options.
Note: When you select “Round” option you need to enter the number of decimals to round.
Power Query is super smart, you know this, right?
There are options which you can use to perform calculations (a lot). Look at the below list.
You can find all these options on the Transform Tab.
To perform any of this calculation you need to select the column and select the option.
21. Group by
Just like pivot tables, it’s an awesome option.
You can find it on the Transform tab.
Let’s say you have a large data set and you want to create a summary table.
Here what you need to do:
- In Transform Tab, click on ‘Group by“ and you’ll get a dialog box.
- Now, from this dialog box select the column with which you want to group.
- After that, add a name, select the operation and, the column where you have values.
- In the end, click OK.
Note: There are also some advanced options in “Group by” option which you can use to create a multiple level group table.
22. Remove Negative Values
Right click on a column and go to Transform and then click on Absolute value.
23. Add a Custom Column
Even though there are a lot of options there in power query.
But, you can also create a custom column using a calculation formula. Follow these steps to create a custom column:
- First of all, go to the Custom Tab ➜ General ➜ Custom Column.
- Here you have a dialog box to create a formula to use in the column (here I want to multiply quantity with price). so enter the formula in formula box.
Quick Tip: When you enter a formula, you’ll have a message below in the dialog box if there’s some error in the formula.
24. Auto Refresh a Query
From all the tips and tricks I have mentioned here this one is the most important.
When you create a query you can make it auto refresh (you can set a timer).
And here are the steps:
- In Data Tab, click on “Queries & Connections” and you’ll get the “Queries and Connection“ pane on the right side of the window.
- Now, right click on the query tick mark “Refresh every second” and enter the minutes.
- In the end, click OK.
You do this for every query but you need to do it separately.
25. Create a Pivot Table from Multiple Workbooks
You can’t afford to avoid POWER QUERY.
If you think like this, a lot of things which we do with formulas and functions or VBA codes can be automated using power query.
And I’m sure the above tips inspire you to use it more and more.
But now you need to tell me one thing.
Which option do you like most about POWER QUERY?
Make sure to share your views with me in the comment section, I'd love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.