You can do so using a named range and a custom function in PowerQuery:
- Name the cell you need to refer - e.g. SourceFile
- Insert a new blank PowerQuery query
- In the PowerQuery editor, go to View > Advanced Editor and paste the following code;
(rangeName) =>
Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
- Name the query to GetValue
Now you can access the named cell in your queries, using GetValue(cellName)
e.g. = Excel.Workbook(File.Contents(GetValue("SourceFile")))