Make a source file relative

When connecting to a source file, the file path is hard coded.
This makes it difficult to share a solution.

The source file path can be made relative to the Excel file. I.e. the solution will work if the source data file is stored in the same folder.

Enter the following formula into a cell.
The formula also creates a hyperlink to the target folder.
This makes it easy to check that the required files are in the folder.

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

Make the cell a named range by clicking on it and then enter a name in the box above column A, for example ‘FilePath’

Make a source file relative

Open the query in the Power Query editor.

Navigate to Home - Advanced Editor

Replace the top lines

let
Source = Csv.Document(File.Contents("C:\Users\Test\Documents\Data.csv"),
[Delimiter=" ", Columns=19, Encoding=1200, QuoteStyle=QuoteStyle.None]),

with the following

let
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FullPathToFile = FilePath & "Employee Timesheet.csv",    
Source = Csv.Document(File.Contents(FullPathToFile),[Delimiter="	", Columns=19, Encoding=1200, QuoteStyle=QuoteStyle.None]),

Note the creation and population of two variables, that are then used in the Source row.

Click on the Done button.

Then Close & Load

The data will be imported from the source file, so long as it is saved in the same folder as the Excel Workbook.

Mearns Learns

Make a source file relative