This post may contain affiliate links. Please read my disclosure for more info. Power Query Course

Power Query – Importing Data

Power Query - Import Data

OK, Let’s get started with Power Query.  The first thing we need to do is import some data.  As this is our first real look at Power Query, we are going to use this as an opportunity to explore the user interface.

This post is structured into two parts:

  1. Using data in the same workbook
  2. Using data from external files and external workbooks.

As we work through the examples, I hope you will see that getting data into Power Query is easy.

Please note that Excel and Power Query are constantly being updated by Microsoft.  Depending on your specific build version of Excel, the names, locations and options of various actions may differ slightly from the examples shown in this post.

Download the example files

It may be tempting just to read the text below, but you will retain the information much better if you work along with the examples.

Subscribers can download the files from the Downloads section.

Excel Downloads

Using data in the same workbook

To use data from within the same workbook, Power Query needs to know the area which the source data occupies.

There are two formats of data areas which Power Query handles well, Excel Tables and named ranges.  The examples below show how to use both of these methods.

Importing data from Tables

Firstly, it is important to note that I am using “Tables” to refer specifically to the Excel Tables feature.  Tables are a vital element of how Excel likes to store data.  The output from Power Query will typically be in a Table format.  So, Tables are fundamental to using Power Query well.  If you are not familiar with Tables, I recommend you spend some time understanding and using them.

The following uses Example 1 – Table.xlsx from the downloads.

Select any cell in the Table.  Then click From Table/Range from the Get & Transform Data section of the Data ribbon.

Data - Import Table Range

The Power Query window will open.  As this is our first look at the user interface, we will spend a bit of time understanding what’s here.

Power Query Interface - First View

The Ribbon – Similar to Excel, the ribbon contains the main commands grouped into separate tabs.  We will be using the ribbon throughout this series, so you will get to see the most common commands in action.

Properties – This is where we name the query.  It is important to give your query a meaningful name, so you know what it does without having to look at the steps.

Applied steps – Much like the macro recorder, each action you take will be recorded in the applied steps.  It is possible to add, remove and edit steps.

Data preview – This area shows a preview of our data, based on the selected applied step.   By right-clicking on the column headers we can access many of the data transformation tools which are also found on the ribbon.

Understanding the applied steps

The applied steps window already contains a small list of items.  These steps are Excel’s attempt to automatically transform the data based on what it believes we want to do.

Source – Identifies the source data (e.g., the Table on the worksheet)

Changed Type – Excel has analyzed the data and tried to apply the correct data type to each column.

PQ First View Data Typespng

These steps have been applied automatically by Excel.  Just be warned, the automatic steps are not always right, so you may have to re-do them.  I have found the Change Type step can often cause frustrating errors later, which are incredibly difficult to find.  Sometimes it is easier to delete the change type step and do it manually.

When we undertake more actions to transform the data, these steps are added to the applied steps list.

Click on each step to watch the data transformations being applied one-by-one.

M Code

M Code is the language which Power Query uses to record the steps applied.

Let’s take a brief look at the M code, to see how Power Query has recorded the steps.  Don’t worry, we will not be spending much time looking at M code, but it will help to understand how Power Query works, and how to make more advanced edits to queries at a later stage.

Click: Home -> Advanced Editor

Home - Advanced Editor M Code

The Advanced Editor window will appear.

M Code Editor

Let’s briefly look at the code and compare it to the applied steps we already have.

Source = Excel.CurrentWorkbook(){[Name="tblSalesData"]}[Content]

The line above is identifying which Excel Table to use as the source.  If we wanted to change the Query to look at a different Table, we could change tblSalesData to the name of the newTable.

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Customer", type text}.......

The line above is changing the data types.  For example, the “Date” column has been changed to a Date/Time type.  This change is based on Power Query’s review of the data in the column.

Close the Advanced Editor, that’s enough M Code for day one.

Adding some steps

Now we will undertake some basic transformations and see how they are added to the applied steps list.

Double click on the “Value” column and change the name to “Amount”.

Change Header

Click on the Data/Time icon and change it to a Date.

Change DateTime to Date

The Date column will now only show the date and not the date and time.

Now look at the Applied Steps window, there are two new steps added for the actions we have completed.

Applied steps updated

Editing the steps

To delete a step, we can click on the X symbol next to the step.

For more changes, you can right-click to see all the options.

Editing the applied steps

These options are generally self-explanatory.  If there are any you don’t understand, then just try them out on some sample data… what’s the worst that’s going to happen 🙂

Rename is probably the most useful command in here and the most underused.  As the steps get longer, it can be difficult to remember what each step does.  It is good practice to rename steps when queries go beyond a few steps (see the screenshot below).

Applied steps renamed

Load the data back into Excel

To complete the process.  We will load the data back into the Excel workbook.

Click: Home -> Close & Load

Close and Load to Excel

A new worksheet will be created, with a Table showing the transformed data.

PQ After Loading data into Excel

You’re probably not blown away yet.  All we have done is load a Table from Excel into Power Query, made a few minor changes, then loaded that data back into Excel. Remember, this is a simple example to show you the basic tool operations.  As we move through this series, we will turn these simple steps into something amazing…. I promise 🙂

Importing data from Standard ranges

If you select a standard range (e.g., Cells A4:E54), rather than a Table, Excel will automatically change those cells into a Table, before importing.  Everything else after that point is identical to the steps above.

Importing data from named ranges

There is another way to tell Power Query the range of cells to be imported; by using named ranges.

The following uses Example 2 – Named Range.xlsx from the downloads.

The easiest way to create a named range is to select all the cells, then type the name into the Name Box and press Return.

Create a Named Range

In the example file, a named range called SalesData has already been created.

Let’s import the data in the named range into Power Query.  The steps are very similar to importing data from a Table (therefore I will not repeat all the information from above, but I will highlight the differences).

Select the named range from the Name Box drop-down list.

Select Named Range

The range of cells will be selected on the worksheet.

Click From Table/Range from the Get & Transform Data section of the Data ribbon.

The data imported will look similar to when using a Table.  But, there may be some additional steps applied.

Named Range - applied steps

In this example, an additional step of Promote Headers has been added – we will be looking at this later in this article.  Click through the applied steps to see what changes have been applied.

The gear icon

Did you notice the small gear icon next to the Promoted Headers step?  This is to indicate where Power Query allows us to change settings.

Click on the gear icon.

Gear icon Click Promoted headers

The Use First Row as Headers window will open.  The window that opens will differ depending on the step type.

Promoted Headers Settings

In our example, we still want the Promote all scalar types options, so we can click OK without making any changes.

Display the Formula Bar

Rather than looking at all the M Code in the Advanced Editor as we did before, we can display the Formula Bar.

Click: View -> Formula Bar

Display Formula Bar

As we click through the steps, the formula bar will change to show the M Code used for each step.  You do not need to change this, but be aware it is there for future usage.

Is it better to use named ranges or Tables?

Having looked at two options, which is the best?  There is not a straight forward answer, as it depends on your source data.

Tables

  • Can auto expand
  • Must be in a structured data format
  • Must have a defined header row
  • Cannot have calculations in the header row

Named ranges

  • Do not auto-expand by default
  • Can contain any number of header rows (though will take more transformations in Power Query)
  • Can have calculations in the header row

Dynamic named ranges can be imported into Power Query too, but that is too advanced for this introductory series.

My advice is to always opt for a Table, unless there is a specific reason not to.

Using data from external files and workbooks

Having looked at data stored in the same workbook as the query, let’s turn our attention to data stored outside the workbook.

So often we open CSV files, text files or Excel workbooks and bring the data into another workbook.  By importing the data directly into the workbook using Power Query, it means that we never need to copy and paste again 🙂  The link to the source data is maintained and it can be refreshed with the click of a button (more on that in the next part).

CSV File

CSV is a very common file format for exports from other systems.  The good news is that Power Query loves CSV files.

The following uses Example 3 – CSV.csv from the downloads.

From the Data ribbon, Click Get Data -> From File -> From Text/CSV

Power Query - CSV Import

The Import Data window will open.  Navigate to the CSV file, select it and click Import.

Power Query - Select CSV File

Power Query will open a new window and display a sample of the data.

CSV Load or Transform dataCSV Load or Transform data

CSV files by definition have a comma delimiter, so there should be no need to change the settings.

To load the data directly into Excel without any changes we can click Load, or to transform the data, click Transform.  All the steps from now on are the same as we have already seen in the sections above.

Text File

Text files are similar to CSV, but can have a variety of data formats.  Let’s look at a Tab delimited Text file to show how it is different.

The following uses Example 4 – Text.txt from the downloads.

Follow the same steps as for a CSV file.  However, instead of selecting comma as the delimiter, select tab.

Power Query - Text Import

If the data you are importing is in another text format, select the appropriate option from the drop-down list.

Then click either Load or Transform Data as required.

Worksheet

Finally, for this section, we will import the contents of an Excel file.

The following uses Example 5 – Excel Workbook.xlsx from the downloads.

From the Data ribbon, Click Get Data -> From File -> Excel Workbook

Power Query - Excel File Import

Select the Excel workbook from the Import Data window and click Import.

The Power Query Navigator window will open.  For this example, just select a single worksheet (for example, January as shown in the screenshot below) and click Transform.  We will be looking at combining multiple files in a future post.

Power Query - Excel File - Select Worksheet

We are now presented with a scenario we have not encountered before.  But it is a scenario which can occur in many circumstances…. Excel doesn’t know which row contains the headers.  Look at the screenshot below, Excel has assumed that row 1 of the spreadsheet contains the headers, but as per the previous examples, the headers should be Date, Customer, Product, Sold By and Value, which are in row 4.

Import Excel - Unknown headers

But don’t worry, we can fix this easily with some simple transformations.

Let’s remove the top 2 rows.  Click: Home -> Remove Rows -> Remove Top Rows

Remove top rows

The Remove Top Rows window will open.  In our example, we want to remove 2 rows, then click OK.

Remove top rows window

Next, we can promote the new top row to be the header row.  Click: Home -> Use First Row as Headers

First Row as Headers

That’s it.  We now have the same data format, as we have in the previous examples.



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

Your email address will not be published. Required fields are marked *