Top 25 Power Query Tips and Tricks You Need to Know Today

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.

Table of Content

Before you start to use these tips there few things you need to clear about. 

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”.

power query tips tricks get transform excel 2016

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.
power query tips tricks install window 2013
  • 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.
power query tips tricks com add in options
  • After that, tick mark “Microsoft Power Query for Excel”.
power query tips tricks tick mark option
  • 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

…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”.

excel power query tips tricks replace name value list
  • 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”.
excel power query tips tricks replace name value open editor
  • Now, in “Value To Find”, enter “Puneet” and in “Replace With” enter “Punit” and after that, click OK.
excel power query tips tricks replace name value enter values
  • 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.
excel power query tips tricks replace name value load data

That’s it.

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.
power query sorting
  • 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.
excel power query tips tricks delete columns select
  • Now, right click and select “Remove”.
excel power query tips tricks delete columns
  • 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.
excel power query tips tricks split column
  • Select the custom from the drop down and enter “-” into it.
  • Now, here you have three different options to split a column.
  1. Left-most Delimiter
  2. Right-most Delimiter
  3. Each occurrence of the delimiter
excel power query tips tricks split column add 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.

excel power query tips tricks split column final table
  • 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”.

excel power query tips tricks rename column

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.

excel power query tips tricks 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”.
excel power query tips tricks merge column button
  • 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.
excel power query tips tricks merge column add separator
  • 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.
excel power query tips tricks transpose

That’s it.

#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.

power query tips tricks remove replace errors

When you right click on the column you'll have two options to deal with it

  • Replace Errors
  • Remove Errors
power query tips tricks remove replace errors option

#10. Change Data Type

Look:

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.

excel power query tips tricks change data type date serial numbers

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.
excel power query tips tricks change data type

That’s it.

excel power query tips tricks change data type dates (1)

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”.
excel power query tips tricks add column click
  • Here you’ll get a blank column. Click on the first cell of the column to get the list of values you can insert.
excel power query tips tricks add column blank
  • Select “Day of Week Name from Date” and click OK.
excel power query tips tricks add column select values

Boom! your new column is here.

excel power query tips tricks add column final

#12. Unpivot Table

I’ve written a complete step by step guide to unpivot data using power query.

excel power query tips tricks unpivot data steps

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.

excel power query tips tricks change text case

#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:
excel power query tips tricks trim clean
  1. TRIM: To remove trailing and leading whitespaces from a cell.
  2. 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.
excel power query tips tricks add prefix suffix
  • Once you click on one of the options you’ll get a dialog box to enter text.
excel power query tips tricks add prefix suffix add
  • And after entering text, click OK.
excel power query tips tricks add prefix suffix final column

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.

But…

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.

power query tips tricks extract values

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.

power query date time only

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.
power query tips tricks date only
power query tips tricks 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)
power query tips tricks combine date time
  • After that select both of the column (Date and Time) and go to transform tab.
power query tips tricks combine date time option
  • Now, from “Date and Time Column” Group go to Date and click “Combine Date and Time”.
power query date time only

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.
power query tips tricks rounding options

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.
power query tips tricks round numbers

Note: When you select “Round” option you need to enter the number of decimals to round.

(20) Calculations

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.

  • Basic
  • Statistics
  • Scientific
  • Trigonometry
  • Rounding
  • Information
power query tips tricks calculations

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.
power query tips tricks group by button
  • Now, from this dialog box select the column with which you want to group.
power query tips tricks group by data
  • After that, add a name, select the operation and, the column where you have values.
power query tips tricks group by options
  • In the end, click OK.
power query tips tricks group by table final

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.

power query tips tricks remove negative

That’s it.

#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.
power query tips tricks custom column button
  • 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.
power query tips tricks custom column options

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.
power query tips tricks auto refresh connections
  • Now, right click on the query tick mark “Refresh every second” and enter the minutes.
power query tips tricks auto refresh connections add time
  • 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

Conclusion

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.

Content Protection by DMCA.com
2018-11-16T06:13:19+00:00

8 Comments

  1. Shobi Imran 29 Sep, 18 at 3:28 pm - Reply

    Great work, Thanks for sharing!

    • Puneet 30 Sep, 18 at 9:13 am - Reply

      You’re Welcome

  2. Steve 22 Sep, 18 at 11:06 am - Reply

    #24. Auto Refresh a Query

    Thanks for all the useful tips. It’s so easy to miss some of these options and just see what you use regularly!

    For Auto Refresh, in Excel 2016 version 1809, I have to right click on a query, select properties and then I can set auto refresh – I didn’t think that was possible without PBI pro!

  3. Brent 13 Sep, 18 at 5:54 pm - Reply

    Great article but the link to the file doesn’t work

    “Download this file to get this list from here.”

  4. Jaishankar 13 Sep, 18 at 4:17 am - Reply

    Hi Puneet, superb article on Power query..I am a power user of Pivot tables….after reading your article I want to run all my data through Power query to resolve data issues and then finally pivot…the process should be much cleaner and quicker…thanks a lot…do you conduct any courses on Advanced Power Query..or do you recommend any particular course or book on the subject..thanks once again.

  5. Dayalan 12 Sep, 18 at 12:03 pm - Reply

    Thanks for a Great Article

    • Puneet 13 Sep, 18 at 3:10 am - Reply

      I’m so glad you liked it. 🙂

Leave A Comment