dynamic source

[daveyc18]

so my source file points to a dated folder and file name

eg. c:\daily files\feb\09\sample09.xlsx

so ofc the next day it'll be different....if i refresh the query, it wont work because it's still pointing to feb 9

is there a dynamic way to refresh this? or is manually editing the code in powery query the only way? not ideal imo

 

[KRice]

I looked into this issue recently and found this idea useful:

 

[daveyc18]

I looked into this issue recently and found this idea useful:

you got me looking..should have just youtubed it...this video is exactly what i needed

 

[daveyc18]

i feel like they should have made this a lot easier to do....i mean, having the file name change daily by date is pretty common for office files

 

[smozgur]

I don't know if the video has the same method but it is always good to keep the written solution in this page for members who'd prefer to read/copy/paste and to help visitors using search feature.

You can do that with a small edit in the M code. Open the Advanced Editor. You will see something like this at the beginning that connects to the workbook:

    Source = Excel.Workbook(File.Contents("c:\daily files\feb\09\sample09.xlsx"), null, true),

What we can do is changing feb, and 09 with the current day's month and day values, and it can be done as shown below:

    strMonth = Text.Lower(Date.ToText(Date.From(DateTime.LocalNow()), [Format="MMM"])), // This is "feb" for February
    strDay = Date.ToText(Date.From(DateTime.LocalNow()), [Format="dd"]), // This is "09" for February 9th
    strFileName = "sample" & strDay & ".xlsx", // This is sample09.xlsx for February 9th
    strPath = "C:\daily files\" & strMonth & "\" & strDay & "\" & strFileName, // Finally, this is the concatenated string as dynamic path
    Source = Excel.Workbook(File.Contents(strPath), null, true),