How To Reference A Named Range In 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 the folder path using a named range in our workbook. This will allow us to easily change the path of the folder and import different sets of files into Excel.

For this you’re 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.

step-001-how-to-reference-a-named-range-in-power-query

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.

step-002-how-to-reference-a-named-range-in-power-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.

step-003-how-to-reference-a-named-range-in-power-query

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.

step-004-how-to-reference-a-named-range-in-power-query

Edit the data query.

  1. Go to the Power Query tab.
  2. If it’s 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.

step-005-how-to-reference-a-named-range-in-power-query

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 red below.
    
    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.

step-006-how-to-reference-a-named-range-in-power-query

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.

step-007-how-to-reference-a-named-range-in-power-query

Now if you have a new folder with new data files in it, you’ll 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.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John, and my goal is to help you Excel!

You’ll find a ton of awesome tips, tricks, tutorials, and templates here to help you save time and effort in your work.

Related Posts

Comments

1 Comment

  1. Shai

    This post is great. It specifies every single step needed to tie between and Excel named range and filter your Power-Query.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃