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.
ftest