How can I reference a cell’s value in PowerQuery

This can be achieved using a named range and a custom function in PowerQuery:

1. Name the cell you need to refer (type in a name into the file left of the formula bar) – e.g. SourceFile
2. Insert a new blank PowerQuery query (PowerQuery ribbon -> From other sources)
3. In the PowerQuery editor, go to View -> Advanced Editor. Remove the existing code and instead paste the following code;

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

4. Name the query to GetValue (Name property in the Query settings pane on the right)

Now you can access the named cell in your queries, using GetValue(cellName) – e.g.

= Excel.Workbook(File.Contents(GetValue("SourceFile")))

If the cell is part of an Excel table, the above is not needed – you can import/access that table’s data directly using the “From Table/Range” button in the “Data” ribbon.