List range Power Query

In a previous post we explored how to import all the files in a folder using power query. But what happens when we get a new batch of data files and we need to import them for analysis? In this post we are going to find out how to reference thefolder path using a named range in our workbook. This will allow us toeasily change the path of the folder and import different sets of files into Excel.

For this youre going to need to be familiar with what we did in the previous post as this post takes off where that one left off. So have a read of this first.

How To Import All Files In A Folder With Power Query

Step 1: Create a query to get the named range.

Firstly create a named range to reference. I have called my named range FilePath and copied in a new folder location.

1. Add a named range by typing in the name FilePath into the name box. Then copy in the folder path into this cell.
2. Go to the Power Query tab.
3. From the Get External Data section press the From Other Sources button.
4. Choose Blank Query.

Now in Query Editor we can add in our M code to create our query function.

1. Type in a new name for the query function. I have used functionGetNamedRange.
2. Go to the View tab.
3. Click the Advance Editor button.
4. Add this code to the editor. let GetNamedRange=[NamedRange] => let name = Excel.CurrentWorkbook[]{[Name=NamedRange]}[Content], value = name{0}[Column1] in value in GetNamedRange
5. Press the Done button.

Close and load the new query.

1. Go to the Home tab.
2. Press the Close & Load button.

Step 2: Edit our data query to use the new function.

Edit the data query.

1. Go to the Power Query tab.
2. If its not already showing press the Show Pane button under the Workbook Queries section to bring up the list of queries.
3. Right click on the data query.
4. Select Edit.

Now we need to edit our query to remove the hard coded folder path location.

1. Go to the View tab.
2. Select Advanced Editor.
3. Replace the path found in quotation marks with this bit of text in redbelow. let Source = Folder.Files[functionGetNamedRange["FilePath"]], #"Added Custom" = Table.AddColumn[Source, "GetData", each functionGetFiles[[Folder Path],[Name]]], #"Expanded GetData" = Table.ExpandTableColumn[#"Added Custom", "GetData", {"Customer ID", "Item", "Order ID", "Quantity", "Total", "Unit Price"}, {"GetData.Customer ID", "GetData.Item", "GetData.Order ID", "GetData.Quantity", "GetData.Total", "GetData.Unit Price"}], #"Reordered Columns" = Table.ReorderColumns[#"Expanded GetData",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "GetData.Customer ID", "GetData.Item", "GetData.Order ID", "GetData.Quantity", "GetData.Unit Price", "GetData.Total"}] in #"Reordered Columns"
4. Press the Done button.

In the query preview you will notice the Folder Path field values have updated.

1. Folder Path field has updated.
2. Go to the Home tab.
3. Press the Close & Load button.

Step 3: Updating the query.

Now if you have a new folder with new data files in it, youll be able to easily change the import folder path and refresh the query.

1. Data has been imported from the files in the new folder.
2. You can now update the path when you need to import a new set of files. Then refresh the query.
3. Go to the Data tab.
4. Press the Refresh All button to refresh the query.