When creating workbook queries using the Query Editor in Excel 2013 or later, it would sometimes be very convenient if the query could use input from one or more worksheet cells to make the query a little more dynamic or user friendly.

The initial workbook query (from Home, Advanced Editor in the Query Editor) could look somethng like this:

let
    Source = Csv.Document(File.Contents("C:\Users\UserName\Downloads\DataWorkshop\Country_Norway_2016.csv"),[Delimiter=";", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Account", type text}, {"Date", type date}, {"Period", type text}, {"Quarter", type text}, {"Amount", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Account",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Account.1", "Account.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Account.1", Int64.Type}, {"Account.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Account.1", "Account"}, {"Account.2", "Description"}})
in
    #"Renamed Columns"

In this case it could be convenient to make the query more dynamic by letting the user enter a source folder path and a file name in two separate worksheet cells.
Create two named cells in any worksheet in the workbook, one named Input_Folder and the other named Input_File.
Make sure that the user is aware that the source folder path must end with a path separator character, or you must add more code to your query to validate the input and add this if it is missing.

After creating the named input cells and adding a source folder path and a filename into them, open the Query Editor and edit the query using the Home, Advanced Editor. The 3 first lines after the let statement is changed in the query below:

let
    SourceFolder = Excel.CurrentWorkbook(){[Name="Input_Folder"]}[Content]{0}[Column1],
    SourceFile = Excel.CurrentWorkbook(){[Name="Input_File"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(SourceFolder & SourceFile),[Delimiter=";", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Account", type text}, {"Date", type date}, {"Period", type text}, {"Quarter", type text}, {"Amount", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Account",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Account.1", "Account.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Account.1", Int64.Type}, {"Account.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Account.1", "Account"}, {"Account.2", "Description"}})
in
    #"Renamed Columns"

Close the Advanced Editor window and return to the Excel workbook.
After changing the contents of the defined input cells you can refresh the query using one of the methods below:

• Click Data, Refresh All or press Ctrl+Alt+F5.
• Right-click on the query name in the Worksheet Queries task pane and click Refresh.
• Right-click in any cell in a table with the query result and click Refresh.
• Using a custom macro to refresh the workbook content.

Like this post