How to get CSV files from a folder dynamically in Excel Power Query!

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.