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.

1 Comment

Comments are closed.