First Dynamic Query and Then Static Again

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

View of Power Query steps in the Avant-garde Editor

Links to topics on this folio

• Scenario – why employ dynamic file paths in Excel Power Query?
• Overview of steps to create dynamic Power Query information source
• Name cells and create named ranges in Excel
• Create Power Query objects to get dynamic file source
• Manual option to edit the source file path for a power query data connection
• More help with Power Query

---

Scenario:

• Nosotros have a batch of data files in a folder. Each calendar month, we create a new month'due south binder, changing the source file path.
• An Excel workbook has split up Power Query data connections to each file.
• Nosotros 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. Encounter our related mail 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. Proper noun the cells (click in the cell, and add a cell label into the proper 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 Avant-garde Editor;
4. Update the file path and file proper noun details in the named cells to refresh the Ability Query data source.

---

Name cells and create named ranges in Excel

To proper noun a cell or range of cells:

Custom name for cell B2 is 'FilePath'.

1. Get to the worksheet where you are going to salvage the custom file path and file name(s).
2. Enter your file path into a prison cell, echo 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 proper name yous want to refer to the prison cell by, and then printing Enter.
6. Your proper noun reference replaces the default jail cell reference.
7. Annotation, your cursor will render to the highlighted cell.
8. To create a named range of cells, select a range of cells, and and so proper name the range (non applicable to this method for creating dynamic file paths in Power Query).

Go to a named jail cell or range in Excel

To become directly to any named prison 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.

↥ Dorsum 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 go the path for the electric current workbook, so another method is required. So, we create custom Power Query objects to link to dynamic file paths and file names.

1. Kickoff, enter your file path and file proper 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 lawmaking shown beneath, under the default 'allow' section:

Open the Power Query Advanced Editor from the Home tab in the Power Query window

Note the comma at the end of the start two lines

The three lines of code explained:

Power Query: create the object to define the FILE PATH

FilePath = Excel.CurrentWorkbook(){[Proper 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 Proper noun

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

• In the example in a higher place:
  • 'File1' is the proper 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, then we apply the Csv.Document() function, see more details from Microsoft help here.
• In the File.Contents() function nosotros add our custom 'FilePath' and 'File1' objects created above.

We tin at present set upward our Ability Query data manipulation steps equally 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

---

Transmission selection to edit the source file path for a ability query data connexion

Simply in example you don't fancy using dynamic paths 🙂 it's pretty piece of cake to manually amend the source reference in a Power Query.

In the Power Query window, click on the settings bike next to the 'Source' step in the Applied Steps pane. You can edit the file path straight, or click on the 'Browse' button to navigate to the file.

The file browser opens when you click on the Source settings wheel

Alternatively, if you click on the actual footstep proper name 'Source' (non the settings cycle), you can edit the file path from the step editor box at the top of the window.

Select the 'Source' step, and edit the file path in the step editor box.

• Become started with Power Query – connect to data and add an Index column
• Power Query dynamic Excel worksheet name
• Power Query – concatenate text and numeric data
• Power Query IF ELSE and IF AND statements

jerezaccur1968.blogspot.com

Source: https://excelquick.com/excel-power-query/power-query-dynamic-file-path/

Share this post