Pinterest Pixel

50 Things You Can Do With Excel Power Query

Excel Power Query is one of the most powerful new features within Microsoft Excel and the easiest to... read more

Download Excel Workbook
Bryan
Posted on

Steps To Follow

Overview

Excel Power Query is one of the most powerful new features within Microsoft Excel and the easiest to learn.

It was first introduced as an Excel add-in in 2013 and was then made available in Excel 2010.  In Excel 2016 it was renamed to Get & Transform and was available (without using an add-in) under the DATA tab in the ribbon menu.  In Excel 2019, it will be renamed to Power Query once again!

You can use Power Query to clean & transform your data that you download from your ERP or accounting system and display it in a report for Excel to work with.

The best thing is that you can reapply the same transformation steps in just ONE single click, saving you HOURS! So next week when you get the same report with updated data, a simple REFRESH will transform your data once again!

I have compiled an interactive Power Query tutorial on the 50 different things you can do with Excel Power Query.

Table of Contents

Watch our free training video on Tips for Using Power Query in Excel


Topic 26: Import Data from Text
Topic 27: Getting Started with M
Topic 28: Excel’s FIND Function with M
Topic 29: Excel’s LEN Function with M
Topic 30: Excel’s RIGHT Function with M
Topic 31: Excel’s LEFT Function with M
Topic 32: Data Type Conversions with M
Topic 33: Advanced Editor
Topic 34: Delete Steps Until End
Topic 35: Comments in Query Steps
Topic 36: Extract Length
Topic 37: Count Rows
Topic 38: Split the Date
Topic 39: Split the Time
Topic 40: Process Flat Data Using Modulo
Topic 41: Format Text
Topic 42: Adding Comments in M (Soon)
Topic 43: Rename a Column (Soon)
Topic 44: Access Function Library (Soon)
Topic 45: Copy and Paste Queries Across Workbooks (Soon)
Topic 46: Create a Sequential List (Soon)
Topic 47: Disable Auto Data Type (Soon)
Topic 48: Split a Long Query (Soon)
Topic 49: Case Insensitive Filtering (Soon)
Topic 50: Add Row Index to Any Query (Soon)

 

1. How To Install Power Query in Excel 2010  

Power Query is a new add-in that was introduced in Excel 2013 by Microsoft and was that popular that they made it backward compatible with Excel 2010.

So what is Power Query?

Well you know when you get data from a file, a data dump/import, or an external data source and it comes into Excel in an ugly, unworkable format?  I can hear you say YEEEES!

Well Power Query accesses, cleans, and transforms all that messy data and displays it in a way that Excel loves and can work with.  The best thing is that the next time you receive the same ugly file, all you need to do is press the Refresh button and your data will automatically be cleaned up and displayed the correct way!

So no more formulas, text to columns, trim spaces, vlookup, find & replace, etc.

You have all that functionality in Power Query at the press of a menu command! Now your life has just become a lot…well, a hell of a lot easier 🙂

Apart from cleaning data, Power Query can append or merge two separate tables together (bye bye VLOOKUP!) as well as create extra columns in your data that can display your custom calculations!

You can use Power Query to perform transformations that would be very complex in VBA or SQL.  So goodbye VBA coding 🙂 🙂 🙂

So how do I get your hands on this super awesome add-in…I hear you say?

First, you need to have Microsoft Office 2010 Professional Plus with Software Assurance.  If you do not have this then you will need to upgrade to Office 365 using this link:

https://products.office.com/en-us/home

 

STEP 1: The first step is to check the Excel bit version that you have on your PC.

To check the bit version, you need to go and open any Excel workbook and go to File > Help and on the far right-hand side, you will see the Version and the bit number in brackets, either (32-bit) or (64-bit).  Once you know this, close out of Excel completely!

How To Install Power Query in Excel 2010

 

STEP 2: You will need to click on the following link and download the Power Query add-in from Microsoft’s website:

https://www.microsoft.com/en-us/download/details.aspx?id=39379

How To Install Power Query in Excel 2010

 

STEP 3: Press the Download button and you will need to select the download bit version based on the bit system your Excel 2010 version is running (from Step 1):

How To Install Power Query in Excel 2010

Press the NEXT button…

How To Install Power Query in Excel 2010

…and this will start the download of the add-in installer .msi file installer on your browser (if you do not see this, go to your browser’s Downloads folder).

 

STEP 4: Click on the .msi file to run the installer:

How To Install Power Query in Excel 2010

 

STEP 5:  This will bring up the Microsoft Power Query for Excel Setup:

How To Install Power Query in Excel 2010

Click the Next button and accept the terms of the Licence Agreement and press Next again:

How To Install Power Query in Excel 2010

You will be asked in which destination folder you want to install the Power Query.

Click Next for the default folder or Change if you want to install it in another folder:

How To Install Power Query in Excel 2010

Click Next and select the Install button:

How To Install Power Query in Excel 2010

Click Yes if it asks you if you want to install Power Query on your computer.  The install will now begin.

You will get the following message once your install has been completed successfully.  Press Finish!

How To Install Power Query in Excel 2010

 

STEP 6: Open a blank Excel workbook and on the ribbon menu you should now see the Power Query tab:

How To Install Power Query in Excel 2010

 

STEP 7: If you do not see the Power Query tab you will need to activate this from the back end of Excel.

You will need to go to File > Options > Add-Ins > and at the bottom you will need to select the Manage drop-down and choose COM Add-ins and hit the Go button:

How To Install Power Query in Excel 2010

This will open the COM Add-Ins dialogue box and you will need to check the box for Microsoft Power Query for Excel and press OK.

How To Install Power Query in Excel 2010

You will now see Power Query on your ribbon!

50 Things You Can Do With Excel Power Query | MyExcelOnline

 

 

2. Install Power Query With Excel 2013  

Power Query is a new add-in that was introduced in Excel 2013 by Microsoft and was that popular that they made it backward compatible with Excel 2010.

So what is Power Query?

Well you know when you get data from a file, a data dump/import, or an external data source and it comes into Excel in an ugly, unworkable format?  I can hear you say YEEEES!

Well Power Query accesses, cleans, and transforms all that messy data and displays it in a way that Excel loves and can work with.  The best thing is that the next time you receive the same ugly file, all you need to do is press the Refresh button and your data will automatically be cleaned up and displayed the correct way!

So no more formulas, text to columns, trim spaces, vlookup, find & replace, etc.

You have all that functionality in Power Query at the press of a menu command! Now your life has just become a lot…well, a hell of a lot easier 🙂

Apart from cleaning data, Power Query can append or merge two separate tables together (bye bye VLOOKUP!) as well as create extra columns in your data that can display your custom calculations!

You can use Power Query to perform transformations that would be very complex in VBA or SQL.  So goodbye VBA coding 🙂 🙂 🙂

So how do I get your hands on this super awesome add-in…I hear you say?

First you need to have Microsoft Office 2013.  If you do not have this then you will need to upgrade to Office 365 using this link:

https://products.office.com/en-us/home

STEP 1: The first step is to check the Excel bit version that you have on your PC.

To check the bit version, you need to go and open any Excel workbook and go to File > Account > About Excel and from the pop up dialogue box you will see the bit number, either 32-bit or 64-bit.  Once you know this, close out of Excel completely!

Install Power Query With Excel 2013

 

STEP 2: You will need to click on the following link and download the Power Query add-in from Microsoft’s website:

https://www.microsoft.com/en-us/download/details.aspx?id=39379

How To Install Power Query in Excel 2010

STEP 3: Press the Download button and you will need to select the download bit version based on the bit system your Excel 2013 version is running (from Step 1):

How To Install Power Query in Excel 2010

Press the NEXT button…

How To Install Power Query in Excel 2010

…and this will start the download of the add-in installer .msi file installer on your browser (if you do not see this, go to your browser’s Downloads folder).

STEP 4: Click on the .msi file to run the installer:

How To Install Power Query in Excel 2010

STEP 5:  This will bring up the Microsoft Power Query for Excel Setup:

How To Install Power Query in Excel 2010

Click the Next button and accept the terms of the Licence Agreement and press Next again:

How To Install Power Query in Excel 2010

You will be asked in which destination folder you want to install Power Query.

Click Next for the default folder or Change if you want to install it in another folder:

How To Install Power Query in Excel 2010

Click Next and select the Install button:

How To Install Power Query in Excel 2010

Click Yes if it asks you if you want to install Power Query on your computer.  The install will now begin.

You will get the following message once your install has been completed successfully.  Press Finish!

How To Install Power Query in Excel 2010

STEP 6: Open a blank Excel workbook and on the ribbon menu you should now see the Power Query tab:

Install Power Query With Excel 2013

STEP 7: If you do not see the Power Query tab you will need to activate this from the back end of Excel.

You will need to go to File > Options > Add-Ins > and at the bottom you will need to select the Manage drop-down and choose COM Add-ins and hit the Go button:

Install Power Query With Excel 2013

This will open the COM Add-Ins dialogue box and you will need to check the box for Microsoft Power Query for Excel and press OK.

How To Install Power Query in Excel 2010

You will now see Power Query on your ribbon!

50 Things You Can Do With Excel Power Query | MyExcelOnline

 

 

3. Inventory Your Files  

Power Query lets you perform a series of steps to transform your Excel data.

One of the possible steps that surprised me was that I could list out the files on my computer! 

This is useful if you want to do some analysis on your file list (i.e. finding a file, checking which files have the biggest size to clear up space).

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Go to Data > New Query > From File > From Folder

(In Excel 2010 & 2013 you need to go to Power Query > From File > From Folder)

Inventory Your Files Using Power Query

 

STEP 2: Type in the path you want to list the files from.  You can also browse for the folder.  Click OK.

Inventory Your Files Using Power Query

STEP 3: This will give you a preview of the file list.  Click Edit to examine it more inside the Power Query editor.

Inventory Your Files Using Power Query

STEP 4: You are now inside the Power Query Window.

You can add more information into your Table by clicking the Icon beside Attributes.

Inventory Your Files Using Power Query

 

For example, we want to include the Size column.  Uncheck (Select All Columns) and check Size.  Click OK.

Inventory Your Files Using Power Query

 

The size column is now shown.

Inventory Your Files Using Power Query

 

STEP 5: Click Close & Load from the Home tab and this will automatically open up a brand new worksheet in your Excel workbook with the new data.

Inventory Your Files Using Power Query

 

You now have your spanking new file list!

Inventory Your Files Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

4. Keep Duplicate Records 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps allows you to take is to keep duplicate records.

We usually remove duplicated lines but if we need to keep and check what the duplicates are, Excel allows us to do that too!

Let’s suppose you have this set of data. You can see that the marked ones are duplicate values, let us keep them!

Keep Duplicates Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Keep Duplicates Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Go to Home > Keep Rows > Keep Duplicates

Keep Duplicates Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated table.

Keep Duplicates Using Power Query or Get & Transform

You now have your new table with the duplicate rows kept!

Keep Duplicates Using Power Query or Get & Transform

How to Keep Duplicates in Excel

50 Things You Can Do With Excel Power Query | MyExcelOnline

5. Split Column By Number of Characters 

Power Query (in Excel 2010 & 2013) or Get & Transform (in Excel 2016) lets you perform a series of steps to transform your Excel data.

See also  Copy and Paste Queries Across Workbooks Using Power Query

One of the steps it allows you to do is to split column by number of characters easily.

This is helpful when you have columns that you want to split by an equal number of characters, say ID numbers.

Let’s suppose you have the following source data below.  We want to split it by 3 characters so we will have 4 parts for each ID.

Let us get to work!

Split Column By Number of Characters Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Split Column By Number of Characters Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Remove Columns Using Power Query or Get & Transform

Excel 2013 & 2010:

Remove Columns Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Select the column you want to split.

Go to Home > Split Column > By Number of Characters

Split Column By Number of Characters Using Power Query or Get & Transform

 

STEP 4: Select 3 for the Number of characters, Split Repeatedly, and click OK.

Split Column By Number of Characters Using Power Query or Get & Transform

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated table.

Split Column By Number of Characters Using Power Query or Get & Transform

You now have your new table with the separated columns!

Split Column By Number of Characters Using Power Query or Get & Transform

How to Split Column by Number of Characters in Excel

50 Things You Can Do With Excel Power Query | MyExcelOnline

6. Duplicate Columns 

Power Query (in Excel 2010 & 2013) or Get & Transform (in Excel 2016) lets you perform a series of steps to transform your Excel data.

One of the steps it allows you to do is to duplicate columns easily.

This is helpful when you have columns that you want to duplicate & make some temporary/permanent changes to it in the Query Editor but not in your source data.

Let’s suppose you have the following source data below.  You can see that the marked column is the one we want duplicated, so let us get to work!

Duplicate Columns Using Power Query or Get and Transform

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Duplicate Columns Using Power Query or Get and Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Remove Columns Using Power Query or Get and Transform

Excel 2013 & 2010:

Remove Columns Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Select the column you want to duplicate.

Go to Add Column > General > Duplicate Column

Duplicate Columns Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated table.

Duplicate Columns Using Power Query or Get & Transform

You now have your new table with the column duplicated!

Duplicate Columns Using Power Query or Get & Transform

 

Duplicate Columns Using Power Query or Get & Transform:

50 Things You Can Do With Excel Power Query | MyExcelOnline

 

7. Use First Row as Headers 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.

One of the steps it allows you to do is to use the first row as headers.

Let’s suppose you have this set of data below.  You can see that the headers are not meaningful to us (Column1, Column2, Column3) so we want to eliminate them and replace them with the first row’s text (Full Name, Country, Rank):

Use First Row as Headers Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Use First Row as Headers Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Use First Row as Headers Using Power Query or Get & Transform

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

We want to change the Table headers with the first row.  Go to Home > Use First Row As Headers

Use First Row as Headers Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the cleaned table.

Use First Row as Headers Using Power Query or Get & Transform

You now have your new table with the brand new table header!

Use First Row as Headers Using Power Query or Get & Transform

How to Use First Row as Headers Using Power Query or Get & Transform

50 Things You Can Do With Excel Power Query | MyExcelOnline

8. Remove Columns 

Power Query (in Excel 2010 & 2013) or Get & Transform (in Excel 2016) lets you perform a series of steps to transform your Excel data.

One of the steps it allows you to do is to remove columns easily.

This is helpful when you have columns that you want to eliminate and do not need in your final report – but do want to keep in your source data.

Let’s suppose you have the following source data below.  You can see that the marked column is the one we want removed, so let us get rid of it!

Remove Columns Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Remove Columns Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Remove Columns Using Power Query or Get & Transform

Excel 2013 & 2010:

Remove Columns Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Select the column(s) you want to remove.  TIP: Hold the CTRL key to select multiple columns.

Go to Home > Remove Columns > Remove Columns

Remove Columns Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the cleaned table.

Remove Columns Using Power Query or Get & Transform

You now have your new table with the column removed!

Remove Columns Using Power Query or Get & Transform

50 Things You Can Do With Excel Power Query | MyExcelOnline

9. Remove Duplicates 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to remove duplicates easily. This removes the human error whenever you try to delete your duplicate data manually!

Let’s suppose you have this set of data. You can see that the marked ones are duplicate values, let us get rid of them!

Remove Duplicates Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Remove Duplicates Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 fill-down-04

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Go to Home > Remove Rows > Remove Duplicates

Remove Duplicates Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the cleaned table.

Fill Down Values Using Power Query

You now have your new table with the duplicate rows removed!

Remove Duplicates Using Power Query or Get & Transform

50 Things You Can Do With Excel Power Query | MyExcelOnline

10. Fill Down Values  

Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to fill data down easily.

You might be wondering when you might need to fill the data down in your table.

Let’s suppose you have this set of data:

Fill Down Values Using Power Query

A lot of values are missing in the Sales Quarter column! It would be a lot of effort to input them one by one.

Let us sort the Sales Month, then the Sales Quarter column to get a better understanding:

Fill Down Values Using Power Query

You can see that we have at least one sales quarter populated for each month.

The technique here is that the Fill Down will copy the value directly above the empty cell and then fill it down the succeeding empty cell.

You can see from the arrows what will happen once we use Fill Down in Power Query.

Now that you know what our game plan is, let us get started!

 

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Fill Down Values Using Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 fill-down-04

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

A) Sort the Sales Month by Ascending order.

Fill Down Values Using Power Query

B) Then sort the Sales Quarter by Descending order.

Fill Down Values Using Power Query

Our data is now ready for the Fill down step.

Fill Down Values Using Power Query

 

STEP 4: Make sure the Sales Quarter column is selected. Go to Transform > Fill > Down

Fill Down Values Using Power Query

 

The missing values are now populated!

Fill Down Values Using Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Fill Down Values Using Power Query

 

You now have your new table with the updated values.

Fill Down Values Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

11. Create Index Columns  

Power Query lets you perform a series of steps to transform your Excel data. One of the most common steps I do, is I want to add an index column that serves as a row counter of my data.

There is an alternative method of using the ROW formula in Excel.

However, if we simply want to keep it as a temporary column for data analysis, we can generate the Row Numbers using Power Query.

Create Index Columns Using Power Query

Thankfully Power Query has an option that allows us to create Index Columns!

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Our sample data consists of Months and multiple Sales values for each quarter.  Let us say we want a column that counts from 1 to 12 next to our data to serve as our row numbers.

(Make sure that your data is first converted into an Excel Table by pressing CTRL+T and OK).

Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

Create Index Columns Using Power Query

Excel 2013 & 2010:

from table

 

STEP 2: This will open up the Power Query Editor.

Within here you need to select Add Column > Add Index Column > Custom

Create Index Columns Using Power Query

 

STEP 3: This brings up the Index Column dialogue box.

Create Index Columns Using Power Query

Set the Starting Index into 1, this would mean the starting number will be 1.

For the Increment, place in 1. This means that every succeeding number will be incremented by one. So we will have 1, 2, 3, 4 and so on..

Click OK.

 

STEP 4: Now you will see your changes take place and the data now has an index column!

Create Index Columns Using Power Query

 

STEP 5: Click Close & Load from the Home tab and this will automatically open up a brand new worksheet in your Excel workbook with the new data.

Create Index Columns Using Power Query

You now have your new table with your index column!

Create Index Columns Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

12. Remove Rows With Errors  

Power Query lets you perform a series of steps to transform your messy Excel data.

One of the most common steps I do, is  to clean my data and remove rows that have erroneous data, like this:

Remove Rows With Errors Using Power Query

Thankfully Power Query has an option that allows us to remove rows with errors!

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Our sample data contains the Sales numbers for each month. However, you can see that there are some months (4 of them) that have invalid data. We want to remove these rows.

Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

Remove Rows With Errors Using Power Query

Excel 2013 & 2010:

from table

 

STEP 2: This will open up the Power Query Editor.

Remove Rows With Errors Using Power Query

 

Within here you need to click the Table icon on the upper left-hand corner of the table.

Then select Remove Errors.

Remove Rows With Errors Using Power Query

 

STEP 3: Now all of the rows with errors will be removed from the table.

Remove Rows With Errors Using Power Query

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the new data.

Remove Rows With Errors Using Power Query

 

You now have your new Excel table without any erroneous rows!

Remove Rows With Errors Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

13. Create Pivot Columns  

Power Query lets you perform a series of steps to transform your Excel data. One of the most common steps I do, is I want to simplify my data and aggregate them together into something like this:

Create Pivot Columns Using Power Query

Thankfully Power Query has an option that allows us to create Pivot Columns!

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Our sample data consists of Sales Quarter and multiple sales values for each quarter. We want to sum them all upper quarter. Let us load our data into Power Query.

(Make sure that your data is first converted into an Excel Table by pressing CTRL+T and OK).

Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

Create Pivot Columns Using Power Query

Excel 2013 & 2010:

from table

 

STEP 2: This will open up the Power Query Editor.

Within here you need to select Transform > Pivot Column

Create Pivot Columns Using Power Query

 

 

STEP 3: This brings up the Pivot Column dialogue box.

Create Pivot Columns Using Power Query

For the Values Column drop-down, select the column name from our data that has the values in it…in our example it will be SALES.

Click on advanced options and this will bring up the Aggregate Value Function.  Here we select how the new cells should be combined.

As we want to show the Sales Totals for each quarter, we need to select the Sum option from the drop-down box.

Click OK.

 

STEP 4: Now you will see your changes take place and the data has now been grouped and summed!

Create Pivot Columns Using Power Query

 

STEP 5: Click Close & Load from the Home tab and this will automatically open up a brand new worksheet in your Excel workbook with the new data.

Create Pivot Columns Using Power Query

 

You now have your new table with the total of each sales quarter.  That’s why they call it POWER QUERY!!!

Create Pivot Columns Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

14. Group Rows and Get Counts  

Power Query lets you perform a series of steps to transform your Excel data.

One of the steps it allows you to take is to group rows and get the counts of each group very easily.

DOWNLOAD EXCEL WORKBOOK

Let’s go through the steps in detail:

 

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Group Rows and Get Counts Using Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Group Rows and Get Counts Using Power Query

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

We want to group this data by Country and show how many times each Country appeared in the table. (i.e. Australia appears 4 times in this table)

Group Rows and Get Counts Using Power Query

 

STEP 4: Within here you need to select Transform > Group By

Group Rows and Get Counts Using Power Query

 

STEP 5: Make sure to select Country for Group By, and select Count Rows for the Operation.

This will group your table by Country value, and count the number of occurrences of each country.

For example, the country of Australia appears 4 times in our table.

Group Rows and Get Counts Using Power Query

STEP 6: Now you will see your changes take place. And the data has now been grouped together.

Group Rows and Get Counts Using Power Query

 

STEP 7: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the new data.

Group Rows and Get Counts Using Power Query

You now have your new table with the counts of each country.

See also  How to Separate Date and Time in Excel

Group Rows and Get Counts Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

15. Reverse Rows  

Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to reverse the order of rows very easily.

DOWNLOAD EXCEL WORKBOOK

 

To provide a quick comparison, it’s not straightforward to reverse rows in Excel without Power Query:

We have our data that we want to reverse the row order:

Reverse Rows Using Power Query

Since Excel does not have a Reverse Row function, we need to add a Helper column manually that has the order listed out (from 1 to 10).

Reverse Rows Using Power Query

We use the Sort function and select Sort Largest to Smallest.

Reverse Rows Using Power Query

The data is now in reverse order.

Reverse Rows Using Power Query

 

Let’s go through the steps in detail on how to do it easily in Power Query:

 

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Reverse Rows Using Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Reverse Rows Using Power Query

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Currently, you can see the Ranks ordered from First to Tenth. We want to reverse the ordering of the rows.

Reverse Rows Using Power Query

 

STEP 4: Within here you need to select Transform > Reverse Rows

Reverse Rows Using Power Query

 

STEP 5: Now you will see your changes take place. The Ranks are now ordered from Tenth to First.

Reverse Rows Using Power Query

 

STEP 6: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated data.

Reverse Rows Using Power Query

You now have your new table in reverse order.

Reverse Rows Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

16. Transpose  

Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to transpose data very easily.

Transposing a data table is basically rotating your data from rows to columnsor from columns to rows. To further explain this concept, screenshots are provided below.

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Transpose Using Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Transpose Using Power Query

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

We want to rotate this data from rows to columns.

Transpose Using Power Query

 

STEP 4: Within here you need to select Transform > Transpose

Transpose Using Power Query

 

STEP 5: Now you will see your changes take place.

Transpose Using Power Query

 

STEP 6: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the transposed data.

Transpose Using Power Query

You now have your new transposed table.

Transpose Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

17. Replace Values  

 

Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to replace values easily.

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Replace Values Using Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Replace Values Using Power Query

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

We want to change the Country Australia into New Zealand. Make sure you have the Country column selected by clicking on the Country header.

Replace Values Using Power Query

 

STEP 4: Within here you need to select Transform > Replace Values

Replace Values Using Power Query

 

STEP 5: This will open up the Replace Values dialogue box.

Place Australia in Value to Find, and New Zealand in Replace With. This will replace all of the Australia values with New Zealand.

Click OK.

Replace Values Using Power Query

STEP 6: Now you will see your changes take place.

Replace Values Using Power Query

 

STEP 7: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Replace Values Using Power Query

You now have your new table with the updated values.

Replace Values Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

18. Split First & Last Name  

There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns.

This is a common task and most people may refer to complex formulas to do this and waste lots of time in the process.

With Power Query, you can split the full name that is in one cell into separate columns with just a few mouse clicks.

Here is how…

DOWNLOAD EXCEL WORKBOOK

STEP 1: Click in your data and turn it into an Excel Table by pressing the shortcut CTRL+T or by going to Insert Table

Split First & Last Name Using Power Query

 

STEP 2: Go to Power Query > From Table

Split First & Last Name Using Power Query

 

STEP 3: This will open up the Power Query Editor.  Within here you need to select Home > Split Columns > By Delimiter

Split First & Last Name Using Power Query

 

STEP 4: This will open up the Split Column by Delimiter dialogue box.

In the drop down box under Select or enter delimiter you need to choose Space

Split First & Last Name Using Power Query

STEP 5: For the Split option, you need to select the default At each occurrence of the delimiter (since there is only one space in each cell’s data) and press OK 

Split First & Last Name Using Power Query

This will split the full name into two separate columns, one for the first name called FULL NAME.1 and another for the surname called FULL NAME.2

Split First & Last Name Using Power Query

 

STEP 6: Now all you need to do is press Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the split columns

Split First & Last Name Using Power Query

You can amend the column headings and here you have your full name split into separate columns…Easy hey!

Split First & Last Name Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

19. Consolidate Multiple Excel Workbooks  

One of the most sought after query from the millions of Excel users around the world is:

How do I consolidate multiple Excel workbooks into one?

There are a couple of ways you can do this, using VBA or complex formulas but the learning curve is steep and out of reach for most Excel users.

Luckily with Power Query, this consolidation task can be done in a couple of minutes!  That’s right, only a couple of minutes.

I show you how below…

DOWNLOAD EXCEL WORKBOOK

STEP 1: Create a New Folder on your Desktop or any directory and name it to whatever you like e.g. 2016 Sales

Move an Excel Workbook in this Folder that contains your Sales data e.g. January 2016.xlsx

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 2: Open a NEW Excel Workbook and go to Power Query > From File > From Folder

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 3: From the Folder dialogue box, click the Browse button

Consolidate Multiple Excel Workbooks Using Power Query

This will bring up the Browse for Folder dialogue box and you need to select the folder you created in Step 1 and press OK

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 4: This will open up the Query Editor.

From in here you need to select the first 2 columns (hold down the CTRL key to select) and Right Click on the column heading and choose Remove Other Columns

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 5: You need to go to Add Column > Add Custom Column

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 6: This will bring up the Add Custom Column dialogue box.

In here you need to name the new column E.G. Import, and within the Custom Column Formula you need to enter the following formula:

= Excel.Workbook([Content])

This will import the workbooks from within the Folder that you selected in Step 3

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 7: You now have a new column called Import.

Click on the Expand Filter and select the Data box only and press OK.  This will import the workbook from the folder

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 8: Click on the Expand Filter from the Import.Data column and select OK.  This imports all the columns’ data from within the workbook

Consolidate Multiple Excel Workbooks Using Power Query

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 9: Now it is time to transform the data by making some cosmetic changes!

Remove the Content column by Right Clicking and choosing Remove

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 10: Select the Import.Data.Column1 and filter out the CUSTOMER heading and press OK.  This will also remove the other column’s headers

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 11: Select the Date column and go to Transform > Data Type > Date

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 12: Select the Sales column and go to Transform > Data Type > Currency

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 13: Rename the column headings to whatever you like by double clicking on the column header, renaming and pressing OK

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 14: Go to File > Close & Load.

Consolidate Multiple Excel Workbooks Using Power Query

 

This will put the data into a new worksheet within your workbook

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 15: You can now Insert a Pivot Table to do your analysis by going to Insert > Pivot Table > New/Existing Worksheet

Consolidate Multiple Excel Workbooks Using Power Query

 

Put the Months in the ROWS and the Sales $ in the VALUES area:

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 16: NOW FOR THE COOL PART!!!!

You can move similar workbooks into the Folder we created in Step 1, say for subsequent months eg. February 2016.xlsx, March 2016.xlsx etc

NB: The Excel Workbooks have to have the same format and number of columns as in the workbook we imported in Step 1

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 17: In your Excel workbook, click on the imported data and this will open up the Workbook Queries pane (If this does not open, go to Power Query > Show Pane)

Click the Refresh button (or you can go to Table Tools > Query > Refresh)

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 18: This will import the February 2016.xlsx and March 2016.xlsx data into the Excel workbook and append it to January’s data

Consolidate Multiple Excel Workbooks Using Power Query

STEP 19: Now you can Refresh the Pivot Table and the new imported data will be reflected

Consolidate Multiple Excel Workbooks Using Power Query

 

Next month all you have to do is drop in the new month’s workbook into the 2016 Sales Folder and Refresh the Query & the Pivot Table to see the results!

THAT IS POWER!!!!

50 Things You Can Do With Excel Power Query | MyExcelOnline

20. Consolidate Multiple Excel Sheets 

Power Query is awesome!

You will see why after viewing this Power Query tutorial.

I get lots of queries from my blog readers asking me if there is a way to easily consolidate multiple Excel worksheets into one.

With Power Query, the answer is YES!

If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates (e.g. January Sales List, February Sales List, March Sales List, etc), then we can easily consolidate all the worksheets into one.

Here is how…

DOWNLOAD EXCEL WORKBOOK

STEP 1: Make sure that each worksheet´s data is in an Excel Table by clicking in the data and pressing CTRL+T

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 2: Click in each of the worksheets data that you want to consolidate and select:

Power Query > From Table

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 3: This will open up the Query Editor and all you have to do here is press Close & Load.  

NB: Make sure to do Step 2 & 3 for each worksheet you want to consolidate

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 4: Select Power Query > Append 

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 5: Choose the Three or more tables option

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 6: Add the tables to append from the Available Tables (from the left) to the Tables to Append (to the right) by selecting and pressing the Add button.

You can also organise the order that you want your consolidated table to appear by moving the Tables up or down

Press the OK button!

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 7: This will open up the Query Editor once again.  Choose Close & Load.

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 8: This will open up a brand new worksheet which will consolidate all the worksheets into one big Table:

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 9: From this consolidate worksheet you can Insert a Pivot Table and do your analysis:

Consolidate Multiple Excel Sheets Using Power Query Append

 

21. Unpivot Data Using Excel Power Query  

In Excel 2016 it comes built in the Ribbon menu under the Data tab and within the Get & Transform group.

Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within Excel, Power Pivot, or the Power BI Designer canvas.

One of the best features is to Unpivot Columns.

What that does is transforms columns with similar characteristics (e.g. Jan, Feb, March…) and puts them in a unique column or tabular format (e.g. Month), which then allows you to do further analysis using Pivot Tables which was not possible before unpivoting.

Here is how this is done:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Highlight your data and go to Power Query > From Table > OK

Unpivot Data Using Excel Power Query

 

STEP 2: This opens the Power Query editor and from here you need to select the columns that you want to unpivot

Unpivot Data Using Excel Power Query

STEP 3: You then need to go to the Transform tab and select Unpivot Columns

Unpivot Data Using Excel Power Query

STEP 4: Go to the File tab and choose Close & Load

Unpivot Data Using Excel Power Query

STEP 5: This will load and open the unpivoted data into a new worksheet with your Excel workbook.  Now you can go crazy with your super analytical work, using Pivot Tables etc

Unpivot Data Using Excel Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

22. Filter Records 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to filter records.

And the best part is, we will use an OR condition! It’s not that straightforward to do in Excel, but it’s magical in Power Query!

Let’s suppose you have this set of data below.

We want to filter records that starts with the letter “Y” OR has a country of “USA”. You can see that the marked ones are our targets, let us keep them!

Filter Records Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Filter Records Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Filter Records Using Power Query or Get & Transform

Excel 2013 & 2010:

Filter Records Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Go to Filter Arrow of Full Name and select Text Filters > Begins With

Filter Records Using Power Query or Get & Transform

Here is where the magic happens!  Select Advanced and make sure your conditions are:

Full Name begins with Y

Or Country equals USA

Click OK.

Filter Records Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated table.

Filter Records Using Power Query or Get & Transform

You now have your new table with the filtered rows kept!

Filter Records Using Power Query or Get & Transform

How to Filter Records in Excel

50 Things You Can Do With Excel Power Query | MyExcelOnline

23. Import Web Data  

 

Power Query lets you clean and transform your raw & messed up data into a format where you can do further Excel analysis with ease.

Firstly, we would need data to play with Power Query, right?  The good thing with Power Query is that there is a multitude of ways to pull in data.

One ways is to get data from the internet by using the importing web data feature.  I will show you how we can parse a webpage and use it for Power Query!

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail using Excel 2016 as our workbook:

STEP 1: Go to Data > New Query > From Other Sources > From Web. 

See also  Split the Time Using Power Query or Get & Transform

(In Excel 2010 & 2013 you need to go to Power Query > From Web)

Import Web Data Using Power Query

 

STEP 2: This will ask for a website where you want to get the data from.

We want to get the stock market indexes from Google finance.

Copy and paste this URL: https://www.google.com/finance

Press OK to continue

Import Web Data Using Power Query

 

STEP 3: Power Query will now try to parse this webpage, and in doing so, has determined that there are multiple tables of data in there.

Here is what Power Query brings back as Table options:

Import Web Data Using Power Query

By clicking on the Table options in the Navigator pane, you will get a preview of the web data on the right-hand side.

Let’s select Table 0 for our example and press Edit.

Import Web Data Using Power Query

 

STEP 4: The data is now imported into the Query Editor window.

Import Web Data Using Power Query

 

STEP 5: We are going to transform Column3 so the data can be easier to analyze.

Select the Column3 heading with your mouse and go to Home > Split Column > By Delimiter.

Import Web Data Using Power Query

 

STEP 6: We want to split the third column into two separate values.  One for the actual number, and another for the percentage.

To achieve this, we need to split the data by the space in between the two values.

Select Space from the delimiter drop-down and press OK.

Import Web Data Using Power Query

 

STEP 7:  Our imported web data has been transformed into our liking.

Import Web Data Using Power Query

 

We are now ready to load it into our Excel worksheet by selecting Close & Load

Split First & Last Name Using Power Query

 

STEP 8:  Now that the data is in your Excel worksheet, tomorrow all you need to do is select the data & press the Refresh button from the Workbook Queries pane on the right hand side.

Import Web Data Using Power Query

 

You will have the latest stock prices & all this without copying and pasting from the webpage! Thank You Power Query 🙂

50 Things You Can Do With Excel Power Query | MyExcelOnline

24. Import Data from XML 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.

But what if your data source is not in your Excel spreadsheet?

It’s very common nowadays to get data imported from a company’s accounting or sales system in the XML format.  If it’s inside a XML (extensible markup language) file, it’s very easy to import data from xml and right into Power Query!  

Let’s suppose you have this set of data from the xml file:

Import Data from XML Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK AND SOURCE FILE

STEP 1:

Using Excel 2016 (screenshot below)

Go to Data > New Query > From File > From XML

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From XML

Import Data from XML Using Power Query or Get & Transform

Select the xml file that contains the data.  Click Import.

Import Data from XML Using Power Query or Get & Transform

Select the XML Data Source.  A preview of the xml data will be shown.  If it looks good, press Edit.

Import Data from XML Using Power Query or Get & Transform

 

STEP 2: This will open up the Power Query Editor.  You can now perform your data manipulation here but we will keep the data as is.

Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the imported table.

Import Data from XML Using Power Query or Get & Transform

You now have your new table from the XML file!

Import Data from XML Using Power Query or Get & Transform

Import Data from XML in Excel

50 Things You Can Do With Excel Power Query | MyExcelOnline

25. Import Data from CSV 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.

But what if your data source is not in your Excel spreadsheet but located on your desktop?

If it’s inside a CSV file – Comma Separated Values which is denoted by a .csv file extension & where the columns are separated by commas – it’s very easy to import data from csv and right into Power Query! 

It’s very common nowadays to get data in the comma-delimited format.

Let’s suppose you have this set of data from the csv file:

Import Data from CSV Using Power Query

DOWNLOAD EXCEL WORKBOOK AND SOURCE FILE

STEP 1:

Using Excel 2016 (screenshot below)

Go to Data > New Query > From File > From CSV

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From CSV

Import Data from CSV Using Power Query

 

Select the csv file that contains the data.  Click Import.

Import Data from CSV Using Power Query

 

A preview of the csv data will be shown.  If it looks good, press Edit.

Import Data from CSV Using Power Query

 

STEP 2: This will open up the Power Query Editor.

Go to Home > Transform > Use First Row As Headers

This will give your table the correct Column Headers.

Import Data from CSV Using Power Query

 

STEP 3: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the imported table.

Import Data from CSV Using Power Query

You now have your new table from the csv file!

Import Data from CSV Using Power Query

Import Data from CSV in Excel

50 Things You Can Do With Excel Power Query | MyExcelOnline

26. Import Data from Text 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.

But what if your data source is not in your Excel spreadsheet?

If it’s inside a text file, it’s very easy to import data from text and right into Power Query!

Let’s suppose you have this set of data from a text file:

Import Data from Text Using Power Query or Get & Transform

DOWNLOAD EXCEL WORKBOOK AND SOURCE FILE

STEP 1:

Using Excel 2016 (screenshot below)

Go to Data > New Query > From File > From Text 

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From Text

Import Data from Text Using Power Query or Get & Transform

 

Select the text file (with extension .txtthat contains the data.  Click Import.

Import Data from Text Using Power Query or Get & Transform

 

A preview of the text data will be shown.  If it looks good, press Edit.

Import Data from Text Using Power Query or Get & Transform

 

STEP 2: This will open up the Power Query Editor.

Go to Home > Transform > Use First Row As Headers

This will give your table the correct Column Headers.

Import Data from Text Using Power Query or Get & Transform

 

STEP 3: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the imported table.

Import Data from Text Using Power Query or Get & Transform

 

You now have your new table from the text file!

Import Data from Text Using Power Query or Get & Transform

 

Import Data from Text Using Power Query or Get & Transform

50 Things You Can Do With Excel Power Query | MyExcelOnline

27. Getting Started with M 

 

Power Query lets you perform a series of steps to transform your Excel data.

There are times when we want to do things that are not built in the user interface. This is possible with Power Query’s programming language, which is M.

To start off, we will do a simple example of merging the first name and second name into a new column. This is possible with the CONCATENATE formula, however I want to use a simple example for you to get a feel of how to use M in Power Query.  Baby steps!

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Getting Started with M in Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 merge-columns-02

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Here we will have our first taste of using M!

Go to Add Column > Add Custom Column

Getting Started with M in Power Query

 

STEP 4: Let us create a simple M expression to combine the First Name and the Last Name.

In the New column name text box, type Full Name

In the Custom column formula, type in: [First Name]&” “&[Last Name] 

(You can alternatively double click in the Available columns names to use the column names in the formula)

The Ampersand (&) will combine the values together, then we added a space in the middle with the double quotes ” “

Click OK.

Getting Started with M in Power Query

Now you will see your changes take place.

Getting Started with M in Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Getting Started with M in Power Query

Woohoo! You now had your first taste of programming using M! Watch out for future posts as we tackle more complex formulas!

Getting Started with M in Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

28. Replicating Excel’s FIND Function with M 

Power Query lets you perform a series of steps to transform your Excel data.  There are times when we want to do things that are not built in the user interface.  This is possible with Power Query’s formula language, which is called M.

Unfortunately, not all of Excel’s formulas can be used in M.

For example, if we want to use the Excel’s FIND Function to find a specific character in text, it is not supported in M.

Let me show you how I can replicate the FIND Function in M!

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Replicating Excel's FIND Function with M in Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 merge-columns-02

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

We want to get the position of the letter O of the Channel Partners, so we need to select the CHANNEL PARTNERS column.

Go to Add Column > Add Custom Column

Replicating Excel's FIND Function with M in Power Query

 

STEP 4: Let us create a simple M expression to replicate the FIND function in Excel.

In the New column name text box, type FIND

In the Custom column formula, type in: Text.PositionOf(

From the Available columns choose CHANNEL PARTNERS and select Insert.

Then finish off the formula by entering  “o”) + 1 

We now have built the following formula:

Text.PositionOf([CHANNEL PARTNERS], “o”) + 1

 

So let us quickly break down what we just did:

  • We are using the Text.PositionOf formula to get the find the letter “o” in the CHANNEL PARTNERS column
  • We added 1 to it because the PositionOf formula starts counting at 0.   That is different to the FIND formula in Excel which starts counting at 1 as the 1st character.
  • So to replicate the FIND formula, we need to add 1 to our formula to make up for the difference.
  • Click OK to confirm.

Replicating Excel's FIND Function with M in Power Query

Now you will see your changes take place.  For example, in “Widget Corp” the first “o” encountered is the 9th character in this text string.

Replicating Excel's FIND Function with M in Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Replicating Excel's FIND Function with M in Power Query

 

Congratulations!  You have used the M formula in Power Query to replicate Excel’s FIND function!

Replicating Excel's FIND Function with M in Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

29. Replicating Excel’s LEN Function with M 

Power Query lets you perform a series of steps to transform your Excel data. There are times when we want to do things that are not built in the user interface. This is possible with Power Query’s programming language, which is M.

Unfortunately, not all of Excel’s formulas can be used in M.

For example, if we want to use the LEN Excel Function to get the length of strings, it is not supported in M.

Let me show you how I can replicate the LEN Function in M!

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Replicating Excel's LEN Function with M in Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 merge-columns-02

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

We want to get the length of the Channel Partners, so we need to select the CHANNEL PARTNERS column.

Go to Add Column > Add Custom Column

Replicating Excel's LEN Function with M in Power Query

 

STEP 4: Let us create a simple M expression to replicate the LEN function in Excel.

In the New column name text box, type CHANNEL PARTNERS (LEN)

In the Custom column formula, type in: Text.Length(

From the Available columns choose CHANNEL PARTNERS and select Insert.

Then finish off the formula by entering  

We now have build the following formula:

Text.Length([CHANNEL PARTNERS])

So lets quickly break down what we just did:

  • We are using the Text.Length formula to get the length of the CHANNEL PARTNERS column
  • Click OK to confirm.

Replicating Excel's LEN Function with M in Power Query

Now you will see your changes take place.

Replicating Excel's LEN Function with M in Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Replicating Excel's LEN Function with M in Power Query

Congratulations! You have used a M formula for replicating the LEN function!

Replicating Excel's LEN Function with M in Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

30. Replicating Excel’s RIGHT Function with M 

Power Query lets you perform a series of steps to transform your Excel data. There are times when we want to do things that are not built in the user interface. This is possible with Power Query’s programming language, which is M.

Unfortunately, not all of Excel’s formulas can be used in M.

For example, if we want to use the RIGHT Excel Function, it is not supported in M.

But I will show you how we can replicate the RIGHT Function in M using Power Query and the Text.End formula!

DOWNLOAD EXCEL WORKBOOK

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Replicating Excel's RIGHT Function with M in Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:
 merge-columns-02

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Select the SALES QTR column.

Go to Add Column > Add Custom Column

Replicating Excel's RIGHT Function with M in Power Query

 

STEP 4: We want to get the last character of the SALES QTR cell values.  For example: 1, 2, 3 or 4

So, let us create a simple M expression to replicate the RIGHT function in Excel

In the New column name text box, type SALES QTR (Shortened)

In the Custom column formula, type in: Text.End(

From the Available columns choose SALES QTR and Insert

Then finish off the formula by entering , 1) 

We now have built the following formula:

Text.End([SALES QTR], 1)

 

So let us quickly break down what we just did:

  • We are using the Text.End formula to get the last X characters of the SALES QTR column
  • We place in 1, to specify that we want the last 1 character.

 

Click OK to confirm.

Replicating Excel's RIGHT Function with M in Power Query

Now you will see your changes take place.

Replicating Excel's RIGHT Function with M in Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Replicating Excel's RIGHT Function with M in Power Query

 

Congratulations! You have used a M formula for replicating the RIGHT function!

Replicating Excel's RIGHT Function with M in Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

31. Replicating Excel’s LEFT Function with M 

Power Query lets you perform a series of steps to transform your Excel data. There are times when we want to do things that are not built in the user interface. This is possible with Power Query’s programming language, which is M.

Unfortunately, not all of Excel’s formulas can be used in M.

For example, if we want to use the LEFT Excel Function, it is not supported in M.

But I have found a way for us to replicate the LEFT Function in M!

DOWNLOAD EXCEL WORKBOOK

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Replicating Excel's LEFT Function with M in Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

See also  Count Rows Using Power Query or Get & Transform

 merge-columns-02

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Go to Add Column > Add Custom Column

We want to get the first 3 characters of the Sales Month:

Replicating Excel's LEFT Function with M in Power Query

 

STEP 4: Let us create a simple M expression to replicate the LEFT function in Excel.

In the New column name text box, type SALES MONTH (Shortened)

In the Custom column formula, type in: Text.Start(

From the Available columns choose SALES MONTH and Insert

Then finish off the formula by entering , 3) 

We now have build the following formula:

Text.Start([SALES MONTH], 3)

 

So lets quickly break down what we just did:

  • We are using the Text.Start formula to get the first X characters of the SALES MONTH column
  • We place in 3, to specify that we want the first 3 characters.

 

Click OK to confirm.

Replicating Excel's LEFT Function with M in Power Query

Now you will see your changes take place.

Replicating Excel's LEFT Function with M in Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Replicating Excel's LEFT Function with M in Power Query

Congratulations! You have used a M formula for replicating the LEFT function!

Replicating Excel's LEFT Function with M in Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

32. Data Type Conversions with M 

Power Query lets you perform a series of steps to clean & transform your Excel data.

There are times when we want to do things that are not built in the user interface.  This is possible with Power Query’s programming language, which is M.

One of the unique characteristics of M is it takes data types very seriously.

For example, if we try to concatenate or combine a text with a number, it will result in an error.  So let us take a look what are our options for data type conversions in M.

DOWNLOAD EXCEL WORKBOOK

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Data Type Conversions with M in Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 merge-columns-02

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Go to Add Column > Add Custom Column

We want to combine the Financial Year (Number) and Sales Qtr (Text):

Data Type Conversions with M in Power Query

 

STEP 4: Let us create a simple M expression to combine the Financial Year and the Sales Quarter.

In the New column name text box, type Financial Quarter

In the Custom column formula, type in: Text.From([FINANCIAL YEAR])&” “&[SALES QTR]

The main point here is we are doing the following steps:

  • We are using the Text.From formula to convert the Financial Year into Text
  • Since Financial Year and Sales Qtr are now both texts, we can combine them together.
  • We can now use the Ampersand (&) to join them together.

Click OK.

Data Type Conversions with M in Power Query

Now you will see your changes take place.

Data Type Conversions with M in Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Data Type Conversions with M in Power Query

Congratulations! You have used a M formula for data conversions!

Data Type Conversions with M in Power Query

There are a lot more data conversion formulas you could use:

  • Date.FromText() – Converts text dates into the date data type
    • ex. Date.FromText(“Jan 12, 2017”)
  • Time.FromText() – Converts text times into the time data type:
    • ex. Time.FromText(“6:55 AM”)
  • Number.FromText() – Converts text numbers into the decimal data type:
    • ex. Number.FromText(“123.34”)
  • Currency.From() – Converts values into the currency data type:
    • ex. Currency.From(500.15)

50 Things You Can Do With Excel Power Query | MyExcelOnline

33. Advanced Editor 

Power Query can let us perform a lot of complex steps with our data.

However wouldn’t it be fun if you could understand better what is happening under the hood? Come join me as we take a look into the Advanced Editor!

We will use the Index Column Post as our starting point.  Please download the Excel Workbook below.

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: For us to view the existing Power Query steps:

Go to Data > Get & Transform > Show Queries (Excel 2016) or

Go to Data > Get & Transform > Show Pane (Excel 2013 & 2010)

Now double click on the Table1 query.

Advanced Editor In Power Query

 

STEP 2: This will open up the Power Query Editor.

Advanced Editor In Power Query

 

You can see the Applied Steps we have with the Index Column workbook you downloaded above.  The steps are broken down as follows:

  • SOURCE: Get the Source Table
  • CHANGED TYPE: Changed the type of columns in the Table
  • ADDED INDEX: Added an Index Column

Advanced Editor In Power Query

 

STEP 3: Now the fun part! Go to Home > Advanced Editor

Advanced Editor In Power Query

 

You will now see our exact 3 steps, in detailed form!

  • Get the Table1 as the Source
  • Change the Column Type of Sales Month to text, and Sales to number
  • Add a new index column called Index that starts at 1 and increments by 1

Advanced Editor In Power Query

 

You are now able to see what’s happening under the hood of your Power Query transformations!

Stay tuned as you join me in editing through the Power Query Advanced Editor next time!

50 Things You Can Do With Excel Power Query | MyExcelOnline

34. Delete Steps Until End 

Power Query lets you perform a series of steps to transform your messy Excel data. And if you make a mistake, it’s very easy to Delete Steps Until the End!

Let’s say you’re deep in thought, creating multiple steps, then all of a sudden you realized midway some of the steps were incorrect.

Our sample Power Query steps to remove rows from our table, and we want to undo those steps!

Let’s go through the steps in detail:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Let us edit an existing query that we want to modify.

Go to Data > Get & Transform > Show Queries

Delete Steps Until End In Power Query

Double click on your Query to open the Power Query Editor.

Delete Steps Until End In Power Query

 

STEP 2: We want to remove all of the Removal steps in our Query. So we will be deleting Step #3 onwards:

Delete Steps Until End In Power Query

Right click on the Step #3 and select Delete Until End.

Delete Steps Until End In Power Query

 

 

STEP 3:  Click Delete.

Delete Steps Until End In Power Query

The steps are removed, and you can see your data is now restored!

Delete Steps Until End In Power Query

 

How to Delete Steps Until End in Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

35. Comments in Query Steps 

Power Query lets you perform a series of steps to transform your messy Excel data. And if you have a whole lot of steps, you can add your own comment in query steps in Power Query!

This will make it easier for you to understand your query, or if another person will use it, they can pick it up quickly!

Let’s go through the steps in detail:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Let us edit an existing query that we want to modify.

Go to Data > Get & Transform > Show Queries

Delete Steps Until End In Power Query

Double click on your Query to open the Power Query Editor.

Delete Steps Until End In Power Query

 

STEP 2: We want to add a comment to one of our steps. Right click on the step that removes the top rows and select Properties:

Comment In Query Steps In Power Query

 

Since this step removes the first two months, type in the Description “Remove January and February” and click OK.

Comment In Query Steps In Power Query

You have saved your first comment in Power Query! You can repeat the same steps for other query steps as well.

 

How to Comment in Query Steps in Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

36. Extract Length 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to extract the length of text.

For our example, let us get the names that have at least 12 characters!

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Extract Length Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Make sure the Name column is selected. Go to Add Column > From Text> Extract > Length

Extract Length Using Power Query or Get & Transform

Now we can get the names that are at least 12 characters long! Click on the Arrow beside the Length Column.

Extract Length Using Power Query or Get & Transform

Go to Number Filters > Greater Than Or Equal To

Extract Length Using Power Query or Get & Transform

Type in 12. Click OK.

Extract Length Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the filtered records!

Extract Length Using Power Query or Get & Transform

You now have your new table with names at least 12 characters long!

Extract Length Using Power Query or Get & Transform

How to Extract Length in Excel Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

37. Count Rows 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to count the number of rows in your query.

And with just one step, you can get the count very easily!

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Count Rows Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Go to Transform> Table > Count Rows

Count Rows Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the row count!

Count Rows Using Power Query or Get & Transform

You now have your new table with a row count of 10!

Count Rows Using Power Query or Get & Transform

How to Count Rows in Excel Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

38. Split the Date 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to split your date into year, month and day for easier processing.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Split the Date Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor. Let us now get the Year, Month and Day.

Make sure the Order Date column is selected. Go to Add Column > From Date & Time > Date > Year> Year

Split the Date Using Power Query or Get & Transform

Make sure the Order Date column is selected. Go to Add Column > From Date & Time > Date > Month > Month

Split the Date Using Power Query or Get & Transform

Make sure the Order Date column is selected. Go to Add Column > From Date & Time > Date > Day > Day

Split the Date Using Power Query or Get & Transform

 

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated records!

Split the Date Using Power Query or Get & Transform

You now have your dates split to Year, Month and Day!

Split the Date Using Power Query or Get & Transform

How to Split the Date in Excel Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

39. Split the Time

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to split your time into hours, minutes and seconds for easier processing.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Split the Time Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor. Let us now get the Hour, Minute and Second.

Make sure the Order Time column is selected. Go to Add Column > From Date & Time > Time> Hour > Hour

Split the Time Using Power Query or Get & Transform

Make sure the Order Time column is selected. Go to Add Column > From Date & Time > Time> Minute

Split the Time Using Power Query or Get & Transform

Make sure the Order Time column is selected. Go to Add Column > From Date & Time > Time> Second

Split the Time Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated records!

Split the Time Using Power Query or Get & Transform

You now have your times split to Hour, Minute and Second!

Split the Time Using Power Query or Get & Transform

How to Split the Time in Excel Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

40. Process Flat Data Using Modulo 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to process flat data via the Modulo calculation in Power Query.

This is a really cool trick that you have to see! In our table, we have this flat sales data, and we want to extract just the names:

Process Flat Data Using Modulo in Power Query

Imagine doing it by hand, it is going to take forever! But with Power Query, it will be done in a flash!

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 2: This will open up the Power Query Editor.

Notice that our flat sales data has a pattern. The pattern “Region, Salesperson Name, Sales” repeats every 3 rows!

Let as add an Index Column to number all of the rows. Go to Add Column > General > Index Column

Process Flat Data Using Modulo in Power Query

 

STEP 3: This is where the magic happens!

Make sure the Index column is selected. Go to Add Column > Number Column > Standard > Modulo

Process Flat Data Using Modulo in Power Query

Since our pattern repeats every 3 rows, let us type in 3. Click OK.

Process Flat Data Using Modulo in Power Query

Now we have our modulo results.

Process Flat Data Using Modulo in Power Query

STEP 4: Notice that the Salesperson name consistently matches the Modulo value 1? We can now extract the names quickly!

Click on the Filter icon of the Modulo column and make sure only 1 is ticked. This will hide the other unnecessary rows.

Process Flat Data Using Modulo in Power Query

 

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated records!

 

Process Flat Data Using Modulo in Power Query

You now have the names thanks to Modulo in Power Query!

Process Flat Data Using Modulo in Power Query

 

How to Process Flat Data Using Modulo in Excel Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

41. Format Text 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to format text with multiple ways.

For our example, let us go over the most common usages of format text:

  • Upper case
  • Lower case
  • Capitalize each word
  • Trim

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Format Text Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor. Let us try to convert to lower case.

Make sure the Full Name column is selected. Go to Add Column > From Text> Format > lowercase

Format Text Using Power Query or Get & Transform

Format Text Using Power Query or Get & Transform

 

STEP 4: Let us try to convert to upper case.

Make sure the Full Name column is selected. Go to Add Column > From Text> Format > UPPERCASE

Format Text Using Power Query or Get & Transform

Format Text Using Power Query or Get & Transform

 

STEP 5: Let us now capitalize each word.

Make sure the UPPERCASE column is selected. Go to Add Column > From Text> Format > Capitalize each word

Format Text Using Power Query or Get & Transform

Format Text Using Power Query or Get & Transform

STEP 6: Let us now try out trimming the text to rid of the extra spaces.

Make sure the Full Name column is selected. Go to Add Column > From Text> Format > Trim

Format Text Using Power Query or Get & Transform

We have now formatted our text in Power Query in multiple ways!

Format Text Using Power Query or Get & Transform

How to Format Text in Excel Using Power Query

50 Things You Can Do With Excel Power Query | MyExcelOnline

Conclusion

In this Power Query Tutorial, you have learned how to clean and transform messy data in Excel and repeat the same process with just a click of a button, every time you change the data.

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, and Macros & VBA!

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

50 Things You Can Do With Excel Power Query | MyExcelOnline

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

50 Things You Can Do With Excel Power Query | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!