Get the file location dynamically

The CELL function can be used in Excel to get the location of the Workbook.

=@CELL("filename",$A$1)

This is the more complex version of the formula, to only get the file path.

=LEFT(@CELL("filename",$A$1),FIND("[",@CELL("filename",$A$1),1)-1)

The FileName and SheetName are hard coded values in the workbook where the query is run from.

All the values in the workbook have been made into named ranges.
This was done by selecting the cell and typing a name into the box above the A column.

Where to set a named range

If the Workbook is stored in OneDrive, a web URL is returned.
Once the Workbook is uploaded to the cloud, the primary location for the Workbook is the cloud.
The Workbook stored on local storage is considered a copy of the cloud file.

A file stored in the cloud

A file stored locally

In Power Query it is possible to test the file location string and use a different method to access a Workbook based on it’s location.

If the location starts with https then use a Web method to get the contents.
Otherwise use a file method to get the contents.

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1],
    SheetName = Excel.CurrentWorkbook(){[Name="SheetName"]}[Content]{0}[Column1],
    FullPathToFile = FilePath & FileName,
    Source = if Text.Start(FullPathToFile, 5) = "https"
        then Excel.Workbook(Web.Contents(FullPathToFile), null, true)
        else Excel.Workbook(File.Contents(FullPathToFile), null, true),
    Sheet1_Sheet = Source{[Item=SheetName,Kind="Sheet"]}[Data],

Mearns Learns

Get the file location dynamically