The Power Query Editor makes it much easier to create queries. However, the folder path is unfortunately hard-coded into the query, which means that if the folder containing the Excel workbook is renamed or moved, the query will stop working.
To solve this, I developed a code snippet in M (Power Query Language) that I systematically use at the beginning of my queries. This code adds the first four steps of the query, after which additional steps can be added using the Editor.
I start by creating a table named Parameters with a column named Folder that will contain the following formula:
=LEFT(CELL("filename",[@Folder]),FIND("[",CELL("filename",[@Folder]),1)-1)
I then create a blank query in which I insert the following code with the Advanced Editor:
let
Source = Folder.Files(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[Folder] & "Data"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content]))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Combined Tables" = Table.Combine(#"Removed Columns"[Custom])
in
#"Combined Tables"
After inserting the code, I can exit the Advanced Editor and use the user interface to add additional steps without any restrictions.