locked
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

  • Hi Carmanet,

    This forum(Excel for Developers) is for issues related to Excel development and your issue is related to PowerQuery, I will move your thread to Where is the Forum For…? so someone could tell you where to go ask your question.

    Thanks for understanding.

     

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 22, 2018 6:25 AM
  • Monday, October 22, 2018 6:30 AM