none
Use cell value for souce filename in powerquery RRS feed

  • Question

  • I need to be able to use the value of a cell as the filename in the source of my powerquery.

    Basically i have a statistics csv file for every day of the month named yyyy.mm.dd.csv. I have a workbook with the first worksheet(called Summary) that has the date in the first column in the same format. so A2=2018.09.01, A3=2018.09.02, and so on for every day in the month. Column A is also a table named "StatsDate" if that helps. then i'll have additional sheets for each day of the month in the same workbook which will be populated by a powerquery to select certain criteria from my daily csv files.

    I would like to just be able to change the month in column A of the Summary sheet and have all the queries automatically use the correct file as the source. so in the end i'll have 1 workbook with a sheet for every day of the month with the stats and a summary page at the beginning that will summarize the data from the rest of the sheets. I've seen other posts about using cells for the dynamic path but really i just want a dynamic filename and i can't seem to modify what i've read in other posts and make it work.

    Here is the beginning of my powerquery and basically in it i want the "2018.09.01" to be extracted from cell A2 of the summary worksheet, then the next sheet will extract its filename from A3 and so on.

    let
        Source = Csv.Document(File.Contents("\\server-data\Shared\Public\Phonestats\2018.09.01.csv"),[Delimiter=",", Columns=30, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = blablablablabla
    in
        #"Filtered Rows"

    I'm fairly new to powerqueries so a real step by step would be the most helpful if possible.

    Thanks.

    Friday, October 19, 2018 2:07 AM

Answers

All replies