Power Query Capabilities

In the last post, I showed you how to bring in data from PDFs into Excel through Power Query. This post will detail some of the rudimentary power query capabilities to get you started.

If you need to know how to access Power Query, just follow along the last post located here.

Otherwise, continue reading to learn about a few of Power Query capabilities.

Power Query editor

At the end of the last post after clicking on the “Transform” button, we ended up on the Power Query editor page, as seen below.

I blew up image 1 so you can see it better because there are a few things to know about the editor.

On the left you can see the various Pages. The one highlighted is the one you are seeing in the middle of image 10.

On the right in the Power Query settings box, you will see a list of steps that have been performed thus far.

Also, there is a menu system along the top of the screen, just like Excel. It is well worth your time to explore the menu system. You won’t understand all of its features yet but as time goes on, you will start to recognize the menu items.

Power Query Capability 1: Remove rows

First, I’m going to remove rows to get at the desired headers (date, value, Party, President, etc) found in row 8. Removing rows is a very common need.

Image 2: Where remove rows tool can be found.

In this instance, I will be removing 7 rows to get at the headers, so that number is entered into the “Number of rows” box.

Image 3: The box where you denote how many rows you want to delete.

Capability 2: Making first row as header

I hope this is self-explanatory.

Image 4: Making your first row as the header

Capability 3: Remove columns

Similar to removing rows, we have the ability to remove columns to what we only need.

Image 5: Where you can find the remove column feature

Capability 4: Rename your headers

As far as I know, there is no renaming feature in the menu system. I’ve been reaching for it through selecting the header and then right clicking to pull up a table of options.

Image 6: Select the header field you want to rename and then right click your mouse to pull up a box of options.

Capability 5: Splitting columns

Sometimes you need to split columns because information has been merged together during the importing process. In my case, the value and the Party column came in together.

Image 7: The second column came in with the value and Party pulled into one column. I want to split this.

After clicking on the “Split Column” icon, you will be asked to show how you want to split the column.

Image 8: Here’s where you tell how to split the information. I need to split by delimiter.

Image 9: Here’s where specific instructions can be provided. I want to split the value from the Party where the space can be found.

In column 2 of Page008, the value came in along with the Party. Note that the value is separated from the Party with a space, so I use that as a delimiter under the Customs box. You can’t really see it in image 18, but I keyed in a space in the box beneath the “–Custom–” box.

Capability 6: Merging all of the tables

After I go through each Pages renaming headers and splitting columns, I can now merge those Pages through the “Append Queries” tool. Append allows you to combine tables with the same layout into one table. Merging allows you to add more headers.

One really important thing I learned: the headers must be the same, including the same capitalization. Power Query regards “date” as different from “Date”. I had to go back and clean up one of my Pages to make the headers consistent.

Image 10: Appending the queries or pages

After all of the prep, I am now ready to append those pages.

Image 11: Here is what comes up when you click on the “Append Queries”

Image 12: Since I have more than 1 Pages to append, I select “Three or more tables”

Images 11 and 12 are showing the steps I am taking to append all of those pages. You can use the shift key to select multiple choices at once and then click on the “Add” button to move everything at once to the box on the right side.

Image 14: After clicking ok to append, I come back to the editor.

Once you click “Ok” during the append process, you will come back to the editor screen with a new table sitting on the left-hand side. In my case, it is called “Append1” and it contains all of the pages in one table. Please double check to make sure the information came in correctly. This was when I found out that the column headers capitalization/non-capitalization mattered.

Now bring data into Excel

When you are ready to pull everything together into the Excel, click on “Close and load” found in the upper left-hand corner.

Notice that closing led to the formation of a couple of sheets, one for each table. You will see multiple tabs at the bottom and multiple tables on the right side in the “Queries and Connections” box. You can delete those tabs. Watch how the right side changes.

Closing comments

There is so much more to Power Query capabilities; I have shown you only a very, very small portion of what Power Query offers. What I have shown are the ones I most typically use to get started, but you can do some very powerful things.