Power Query dynamic file path

Power Query dynamic file path

Purpose: use a dynamic file path for a Power Query or Power BI data source, taken from a cell in the current workbook.

View of power query code in the Advanced Editor window
View of Power Query steps in the Advanced Editor
Links to topics on this page

Scenario:

Power Query steps in the applied steps window
  • We have a batch of data files in a folder. Each month, we create a new month’s folder, changing the source file path.
  • An Excel workbook has separate Power Query data connections to each file.
  • We enter the file path and file names into another worksheet in the workbook, creating dynamic file paths for the Power Query connections.

This page shows how to connect to a csv file. See our related post for linking to an Excel workbook in Power Query.

Overview of steps

  1. Enter file path and file names into cells in a separate worksheet in the Excel workbook;
  2. Name the cells (click in the cell, and add a cell label into the name box);
  3. Create custom objects at the start of the Power Query which define the file paths using the named cell references, using the Advanced Editor;
  4. Update the file path and file name details in the named cells to refresh the Power Query data source.
Illustrating file path and file name details in an Excel worksheet for linking to in a Power Query

Name cells and create named ranges in Excel

To name a cell or range of cells:
A named cell in Excel
Custom name for cell B2 is ‘FilePath’.
  1. Go to the worksheet where you are going to save the custom file path and file name(s).
  2. Enter your file path into a cell, repeat for additional file paths.
  3. Enter your file name into a cell, repeat for additional file names.
  4. Click in each cell, then click in the ‘Name box’ (top left box above the worksheet grid).
  5. Enter the name you want to refer to the cell by, then press Enter.
  6. Your name reference replaces the default cell reference.
  7. Note, your cursor will return to the highlighted cell.
  8. To create a named range of cells, select a range of cells, and then name the range (not applicable to this method for creating dynamic file paths in Power Query).
Go to a named cell or range in Excel

To go directly to any named cell or range in an Excel workbook, click in the ‘Name box’, enter the name, and press Enter. The target worksheet is displayed and the named cell or range is highlighted.

↥ Back to overview steps

Create Power Query objects to get file paths from cells in the current workbook

Note that Power Query doesn’t provide a built-in way to get the path for the current workbook, so another method is required. So, we create custom Power Query objects to link to dynamic file paths and file names.

  1. First, enter your file path and file name details into a worksheet in your Excel workbook, as described above.
  2. Open a blank query, open the Advanced Editor window, and
  3. Enter the three lines of code shown below, under the default ‘let’ section:
Open the Advanced Editor in a Power Query
Open the Power Query Advanced Editor from the Home tab in the Power Query window
Power Query code in the Advanced Editor window
Note the comma at the end of the first two lines

The three lines of code explained:

Power Query: create the object to define the FILE PATH

FilePath = Excel.CurrentWorkbook(){[Name=”FilePath”]}[Content]{0}[Column1],

  • In our example above:
    • FilePath= creates an object named ‘FilePath’;
    • Name="FilePath" is referring to the cell in the worksheet that we have named ‘FilePath’.
Power Query: create the object to define the FILE NAME

File1 = Excel.CurrentWorkbook(){[Name=”File1″]}[Content]{0}[Column1],

  • In the example above:
    • ‘File1’ is the name of our custom Power Query object, and is the named cell in the worksheet containing the file path and file name
    • Excel.CurrentWorkbook() is the Excel function which points to contents within the workbook.
Power Query: define the DATA SOURCE as a combination of the file path and file name

Source = Csv.Document(File.Contents(FilePath & File1))

  • In our example, we’re importing data from a csv file, so we use the Csv.Document() function, see more details from Microsoft help here.
  • In the File.Contents() function we add our custom ‘FilePath’ and ‘File1’ objects created above.

We can now set up our Power Query data manipulation steps as usual.

When the source data changes, we can update the file path and file name details in the worksheet, and refresh the Power Query.

↥ Back to overview steps

Manual option to edit the source file path for a power query data connection

Just in case you don’t fancy using dynamic paths 🙂 it’s pretty easy to manually amend the source reference in a Power Query.

In the Power Query window, click on the settings wheel next to the ‘Source’ step in the Applied Steps pane. You can edit the file path directly, or click on the ‘Browse’ button to navigate to the file.

Manually editing a Power Query data source connection
The file browser opens when you click on the Source settings wheel

Alternatively, if you click on the actual step name ‘Source’ (not the settings wheel), you can edit the file path from the step editor box at the top of the window.

Manually editing a Power Query step in the step editor box
Select the ‘Source’ step, and edit the file path in the step editor box.