NOTE: This post was created using the November 2014 version of Power Query. In later versions, some of the screens or functionality could be different.

In this post, we will demonstrate a few of the options available for importing data into Excel with Power Query. There are a lot of options available, many with their own options. An exhaustive examination of all of them is outside the scope of this post and this series. Since a common use for self-service BI tools in Excel is to mash up data from your Enterprise data sources with data from elsewhere, we will focus on a few sources common in that situation.  For now, we will be covering the following:

1. From Table

2. From File (Text File)

3. From Database (From SQL Server Database)

4. From Web

If we are not covering a source you are wanting to learn about, please follow this link to the Office Online documentation from Microsoft. Also, feel free to comment on this post as well. A series that walks through different sources and how to use them is certainly not out of the question.

1. From Table

The From Table source allows you to take data residing in an Excel worksheet and import it into the Excel Data Model where you can easily relate it to other data using Power Pivot.

In this example, we have a list of the Nobel Prize categories along with ID values that have been assigned to them matching our internal database. This list is formatted as a table, but it does not have to be as the From Table source allows for specifying a range of cells as well. Figure 1-1 shows the table of data in Excel.

Figure 1-1

Since the data in this table is a list of Categories, I named the table Category. I highly recommend naming objects appropriately right away. The sooner you give objects the names you want them to have, the easier your life will be (as it relates to that data anyway).

1-1. With a cell in the table selected, click the Power Query tab of the Excel Ribbon, as shown in Figure 1-2.

Figure 1-2

1-2. On the Power Query Ribbon, click the From Table button, as shown in Figure 1-3.

Figure 1-3

This will launch a new query based on that table and open the Query Editor, as shown in Figure 1-4.

Figure 1-4

It’s that simple. Note that the name of the Query matches the name of the Table in Excel. Bonus.

2. From File (Text File)

There are a handful of options for using the From File source. Those options are shown in Figure 2-1.

Figure 2-1

For this example, we will use the From Text option to use a .txt file as a source.

2-1. Click the From Text option as shown in Figure 2-1. This will open a file browser window for selecting the source file as shown in Figure 2-2.

Figure 2-2

We will be using a text file I created listing out the Nobel Prize winners, but you can use any text file you want that has tabular data in it. By “tabular” I mean that a file containing paragraphs of text is not a great choice. Rather, you want something where the information is laid out in columns and rows.

2-2. To select the file and continue, you have a few options.

a. Double-click the file

b. Click the file and hit the <Enter> key on your keyboard

c. Click the file and click the Open button

Whichever option you choose, Power Query will launch a new query and open the Query Editor for it, as shown in Figure 2-3.

Figure 2-3

Notice that the name of the Query is Query1. Unlike the From Table source, the From File source does not, as of the November 2014 release, automatically use the file name as the Query name. Also notice that there are three steps in the APPLIED STEPS pane. The From File source WILL automatically detect (with great accuracy in my experience) whether the first row in the file makes suitable column headers. If so, it will apply that transformation so you do not have to. The Change Type step looked for numeric data and converted any columns with just numbers into an appropriate numeric format.

3. From Database (From SQL Server Database)

Power Query can extract data from many different database systems. The full list is shown in Figure 3-1.

Figure 3-1

In this example, we will pull data from a SQL Server database.

3-1. Click the From SQL Server Database Option. This will launch the dialog shown in Figure 3-2.

Figure 3-2

You will need the name of a SQL Server database engine instance. In my case, I have a default instance running on my laptop, so I can use “localhost” as my server name. You have the option to enter a database name as well. If not, you will have to navigate to the database you want in a future step. You also have the option of entering a Transact SQL query to extract your data. For this example, we will start by just entering the server name and continuing.

3-2. Enter the name of the database server (or localhost if you are working on a machine with the database engine instance you want) and either hit your <Enter> key or click OK. This will launch the window shown in Figure 3-3.

Figure 3-3

Connecting to a SQL Server database requires authentication. The default is to just use the credentials of the user currently logged on. However, if you have specific credentials for SQL Server, you can click the Database option shown in Figure 3-3 and enter that username and password there. In this example, I will use my Windows login so I can just click Save.

3-3. Click Save to use the method you have chosen.

If you are connecting to a SQL Server instance that has not been configured to use encrypted connections, you should receive the message shown in Figure 3-4 the first time you connect to that server.

Figure 3-4

If you do see this message, and are not going to use data that would put privacy at risk, you can click OK and continue. In my case, I am connecting to a database engine instance on my laptop and using public data, so this is not an issue.

If the server you are connecting to has been configured to use encrypted connections, then this message will not appear. Whether you get this message and click OK or you do not get this message at all, you should see a Navigator pane at the right listing the databases on the server, as shown in Figure 3-5.

Note: The instructions for configuring your SQL Server database engine to allow encrypted connections can be found here.

Figure 3-5

On my server, I will use the AdventureWorksDW2014 database. You can use whatever database you like.

3-4. Click the triangle to the left of the database you want to use, as shown by the arrow in Figure 3-5 above. This will expand that node to reveal the tables and views in that database, which, for the AdventureWorksDW2014 database will resemble Figure 3-6.

Figure 3-6

3-5. Hover the cursor over the object of your choice to reveal the Peek for that object. The Peek for the DimDate table is shown in Figure 3-7.

Figure 3-7

3-6. Click the Edit button, shown by the arrow in Figure 3-7, to open the query editor for a new query based on the selected object. A new query based on the DimDate table is shown in
Figure 3-8.

Figure 3-8

Notice that Power Query automatically used the name of the source object as the name of query. Cool. You can also see that there are two query steps in this query so far, the Source and the Navigation.

Clicking on the gear icon to the right of the Source in the APPLIED STEPS will show the server connection screen shown above in Figure 3-2 and repeated here as Figure 3-9.

Figure 3-9

Clicking the gear icon to the right of the Navigation step will reveal the object explorer from
Figure 3-6 repeated here as Figure 3-10.

Figure 3-10

Steps with gears are really helpful as they allow you to see the specific choices that were made in that step. Many steps perform simple enough actions that a gear icon is not necessary.

4. From Web

Before I get into this one, I think a quick note is in order. I used this as the foundation for my PASS Summit 2014 presentation, Power Query: Data Chemistry for the Masses. Less than two weeks before Summit, I followed (largely by accident) the following steps to consume a API using JSON,  without really knowing how to explain what JSON even was, with ZERO code. I was amazed. Right then and there, I revamped my demo to have this as a foundation. I had the great pleasure of sharing my joy about this with members of the Power Query team during Summit. That was awesome to let people who work extremely hard to produce great products know that their work is appreciated. I recommend you do that same if you get the opportunity.

NOTE: Revamping a demo really close to when the presentation is scheduled is not a great idea, particularly for an inexperienced speaker. If I may borrow a line from Charlie in Top Gun when analyzing flight footage of Maverick performing a split S, “The encounter was a victory, but I think we have shown it as an example of what not to do.”

The From Web source is useful when we already have a URL for a web page from which we want to pull data. It is through this source that we can utilize data sources that are not in the list of sites indexed for use by the Online Search source. The list of public data sources used by Online Search can be found here.

The following is the URL for the API from the Nobel Prize website allowing us to pull information directly from the authority on this data. The URL will communicate with the API using JSON (Java Script Object Notation). Feel free to follow the link in your browser and see the raw data.

http://api.nobelprize.org/v1/laureate.json

4-1. Click the From Web button on the Power Query Ribbon, shown with the arrow in Figure 4-1.

Figure 4-1

This will open the From Web dialog shown in Figure 4-2.

Figure 4-2

4-2. Enter the URL as shown in Figure 4-2 then hit the <Enter> key or click OK. This will establish a new query using that data returned as a source, as shown in Figure 4-3.

Figure 4-3

I know what you’re thinking. “Mark, dude, what the heck kind of dataset is that?” JSON is hierarchical. What we see right here is just the top level of the hierarchy, ie the List itself.

4-3. Click the Into Table button shown with the arrow in Figure 4-3. This will convert that JSON List object into a Table for easier use in Power Query and result in what is shown in Figure 4-4.

Figure 4-4

That’s not much better. However, we now see a handy little “Expand” button, shown by the arrow in Figure 4-4. This will “expand” our list items into Records to make them easier to use.

4-4. Click the Expand button. This will reveal the Records, as shown in Figure 4-5.

Figure 4-5

OK. We’re getting there.

4-5. Click the Expand button again to expand another level. This will produce a new pane as shown in Figure 4-6.

Figure 4-6

There we go. That will give us far more useful columns than we have seen thus far. If we click OK with the “Use original column name as prefix” option checked, each column name will be prefaced with “Value.” As such, the firstname column would be imported as Value.firstname. As a matter of personal preference, I prefer NOT to have that happen here, even though I do appreciate having the option. So, for this example, we will uncheck that option.

4-6. Click the “Use original column name as prefix” checkbox, shown by the arrow in Figure 4-6, to deselect it.

4-7. Hit your <Enter> key or click OK. This will produce what is shown in Figure 4-7.

Figure 4-7

We are starting to see some meaningful data now. We have a single row for each Nobel Laureate.

4-8. Scroll over all the way to the right to see the columns that are currently on visible. The result should resemble Figure 4-8.

Figure 4-8

We see our friendly Expand button again. The reason there is another level here is that some Laureates have one more than one Nobel Prize. Imagine that. “Oh, you only have ONE Nobel Prize? How cute.”

4-9. Click the Expand button, resulting in a separate record for each awarding of a Nobel Prize. The result should resemble Figure 4-9.

Figure 4-9

Now we have a row for each awarding of each prize. Grand.

4-10. Click the Expand button again to reveal additional columns about each awarding of each prize, as shown in Figure 4-10.

Figure 4-10

Well, how cool is that? Power Query remembered our preference from last time and left the “Use original column name as prefix” deselected.

4-11 Click OK of hit the <Enter> key to add the new columns to our dataset. The result is shown in Figure 4-11.

Figure 4-11

We now have all the columns we need. We could expand the affiliations column but I think we have made the point here.

Just to recap, we just consumed a JSON API using just a few clicks of the mouse and ZERO code. That’s right. We didn’t write a single line of code ourselves. That is just awesome in my humble opinion. We will be using the dataset create in steps 4-1 through 4-11 for much of this series, so if you want to save this by clicking the Close & Load button, shown by the arrow in Figure 4-11, you can do so. You can then save the Excel file and the query will be saved as well.

For now, we will move on to managing columns and rows.