Power Query – source reference as filepath in cell

This short tutorial describes the Power Query (Get and Transform) connection to a file, whose name and path is written in a specific cell. This can be used when you needs to simply control, which file is used as source, without even opening Power Query.

First of all add a table with one column and one row and type filepath to its only cell. The name of table is going to be, lets say, “Path_table” and the name of its only column is going to be “Path_column”.

The query getting data from variable path will be like this:

• Excel.Workbook(
  File.Contents(Excel.CurrentWorkbook(){[Name = “Path_table“]}[Content][Path_column]{0}),
  null,
  true
  )

Or, more practically, we can split it into more queris – getting of value and its use.

• let
     Source = Excel.CurrentWorkbook(){[Name=”Path_table“]}[Content][Path_column]{0}
  in
     Source
• let
     Source = Excel.Workbook(File.Contents(path_value), null, true),
     navi = Source{[Item=”prehled aut”,Kind=”Sheet”]}[Data],
     headers = Table.PromoteHeaders(navi, [PromoteAllScalars=true])
  in
     headers

Obviously, replace the red texts with your names of table and column.

Analogically, you can use this to define a path to folder.

If you need relative path instead of the absolute one, click here.