How to Clean Up and Import Data Using Power Query in Excel

Power Query Quick View

• What is Power Query?
• Clean Excel data with Power Query
• Import and reorganize data using Power Query

In 2010, Microsoft added another technical term to Excel's term list - Power Query, but it's not as complicated as it sounds. In fact, once you start using it, the learning curve is not steep, provided you have at least an entry-level understanding of Microsoft Excel.

What is Power Query?

Power Query was originally introduced as an add-in in Excel, but given its usefulness, it has become a native tool for Excel since 2016.

In short, the main purpose of Power Query is: Save you time processing data. More specifically, it can be used to clean up data already in Excel, import and organize data from many different places, or merge various files before loading the data into a specific location of your choice. You can also refresh the data to make sure you have the latest version.

Excel's Power Query tool can be found in the Get and Convert Data group on the Data tab.

In this article, I will show you how to use Power Query to clean up your data and how to import and organize your data from another spreadsheet.

Clean Excel data with Power Query

I always use Power Query to clean up data in my Excel tables. Suppose you have copied the list of email addresses copied from the CC field of the Outlook email to cell A1, and you want the list to be converted to a table containing the first (or title), last name, and email address. While you can use Excel functions to do this, it is easier to do this in Excel's Power Query Editor (and the learning curve is less steep!).

It should be noted here that this is just a simple example of how you can use Power Query to organize your data. If you have never used Power Query before, follow these steps to learn its most basic way of working, and then you can try using other Power Query tools to organize more complex Excel data.

First, open the Data tab on the ribbon, and then click Self Table/Area in the Data group.

Then, in the Create Table dialog, make sure the correct cell is selected and tell Excel whether your data contains the title--in my case it didn't, so I'll uncheck the box- —And click OK.

Excel will then open the Power Query Editor, where you can do a lot of weird things, including organizing your data.

The first step is to split each email address into its own rows, so right-click the column header, hover over "Split Columns", and click "By Separator". You can choose other ways to split the data, such as after a specific number of characters or between capital letters. However, in my case the email address is separated by a semicolon, so the separator option works.

In the Split Columns by Separator dialog box, select "Semi-colon" in the drop-down list. Then, click Advanced Options and check Rows because you want to split your email address into separate rows instead of separate columns. Now, click OK.

This works well—the email address has been split into separate lines, and Power Query uses semicolon separators to determine when an email ends and when the next email begins.

Note that the Query Settings pane on the right side of the screen records each step you perform. If you get it wrong, just delete the relevant steps and return to your previous location!

After double-clicking on the column title and typing an email address, you need to extract the first (or title) and last name into a separate column. There are multiple ways to do this, but the best way is to right-click on the column header and select "Add Columns by Example".

Now, since the first name in my list is Captain, I will type it into the column that appears on the right side of my screen and press Enter. Look! The Power Query Editor recognizes one pattern and suggests the remaining names to complete my columns. After checking that you are satisfied with the suggestions, click OK.

Then, perform the same process for the last name and rename the column title in the Power Query Editor.

You can now reorder the columns by clicking and dragging them. In my case, I want the email address column to be on the right side of the name.

Name this new table in the Query Settings pane before loading it to your spreadsheet.

Now, click the Close and Load drop-down menu in the ribbon and select Close and Load to.

In my case, I want it to load as a table into cell A4 in an existing worksheet, so before clicking OK, these are the ones I will select in the Import Data dialog box Options.

To select the cell that the table will place, place the cursor in the field box marked "3" in the screenshot below, and select the cell with the mouse.

If you need to make any changes to the table, I can double-click the query in the Query and Connect pane.

Similarly, if I modify the original list copied from Outlook (such as adding another email address), I can update my Power Query table by right-clicking anywhere in the table and clicking Refresh.

Import and reorganize data using Power Query

Power Query can also be used to import data from various locations, such as PDFs or websites. In this example, I'll show you how to import and manipulate data from another spreadsheet, which is especially useful if you want to extract very specific arrays of data from a large dataset, or if you want to reorganize how the data is laid out .

As with the example above, I'll show you a very simple example of doing this with Power Query, which you can then use to use the tool.

First, open the Data tab, and click Get Data>From File>From Excel Workbook.

Then, use the Import Data window to find the file from which you want to import data and click Import. This launches the Navigator window, which previews the data you can import from the specified spreadsheet. In my example, I'll click "Sheet 1", which is where the data I want to import is located and then click "Convert Data".

In the loaded Power Query editor, I can immediately click "Save and Load" in the upper left corner, but before doing so, some changes are needed.

First, I need to promote my first row to the column header so that the first row of the spreadsheet becomes the first row of data. Conveniently, there is a button that does this immediately: Use the first line as the title, located in the Convert group on the Start tab.

Secondly, I want a new row of data for each store and every month so that I can analyze them separately. Currently, my first two columns are fine, so I can select them by holding down the Ctrl key while clicking on each column header. I will then right-click on either column title and click Cancel Perspective Other Columns.

Now, each store has its own line every month.

Finally, before I load it into the spreadsheet, I need to organize my column headers. If you want to do the same, double-click any title to rename them and click the symbol to the left of each column header to define the data type. This forces Power Query to tag any issues in the data, such as a cell in the Month column does not contain a month.

Now, after you name your table in the Query Settings pane (to the right of the Power Query window), click Close and Load > Close and Load to, and select the one you want the table to display Location.

Remember that a key benefit of using Power Query compared to simply copying and pasting data is that if the source data changes, you can update the data you reprocessed in a new spreadsheet. To make sure you have the latest version of the data, just right-click on the new table and click Refresh.

---

You can also import tables from the web using Power Query. For example, you might want to import a league standings that may change every weekend, so instead of manually copying and pasting data and making the league standings out of date quickly, use Power Query to help you make sure you have the latest version of data.

The above is the detailed content of How to Clean Up and Import Data Using Power Query in Excel. For more information, please follow other related articles on the PHP Chinese website!