Excel Query with dynamic file path

Question

I have a query in excel with a specific path to a CSV file. I need to modify the path because part of it will come from data placed in a cell on the same spreadsheet.

let
    Source = Csv.Document(File.Contents("C:\Users\PC\Downloads\MY ETF.csv"),[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Symbol", type text}, {"Company Name", type text}, {"Opinion Date", type date}, {"Opinion", type text}, {"Opinion Price", type number}, {"Power Rating", Int64.Type}, {"Strength", type text}, {"Close", type number}, {"Stop", type number}, {"Support Range", type text}, {"Resistance Range", type text}, {"Slow %K", Int64.Type}})
in
    #"Changed Type"

So I would like to change the "PC" to whatever value that is in cell A1. Rest remains the same.

Thanks

Answer 1

When you assign a name, say "myPath", to the cell containing the path, in your case A1, you can read the value from Power Query via

Excel.CurrentWorkbook(){[Name="myPath"]}[Content]{0}[Column1]

One way to assign a name to cell A1 is to click into the text-field showing the cell's name "A1"

and simply to type the name you would like to give to that cell ("myPath") and press Enter.