Setting Up Dynamic Source File Name

[negbc]

I'm looking to create a source file path to which the file name can change dynamically by date. I will have a daily extract to a folder, with the same naming convention "Name.YYMMDD" with that date being the extract date. In power query I want to connect, for example, to the file the is -7 days from today, or -14 days from today. Is there a way to format the file path so it changes everyday when the report freshes to file name that has the date corresponding to the date -7 days from the current day?

[dufoq3]

Hi @negbc,

= "Name." & Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -7), [Format="yyyyMMdd"])

[negbc]

Would this replace the entire source code? or would I still need some of the elements that as shown below and replace the file path with the code you provided?

[abc44]

Hello,

Were you able to have a solution on your question?
I am looking for the same code, and I can't find how to include the code mentionned in the one I am using.
I need to include : 

= "Name." & Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -7), [Format="yyyyMMdd"])

in 

= Csv.Document(File.Contents("C:\Users\NAME\Desktop\DATABASE\DATA123\123OTB.110924.txt"),[Delimiter=" ", Columns=19, Encoding=1200, QuoteStyle=QuoteStyle.None])

The  code above is the source code I have when I go manually looking for the file, so I guesses I just needed to replace the name of the file by the code suggested? But so far it didn't work I have the below error.

Expression.SyntaxError : Jeton Comma

Any idea what is wrong?

Thanks a lot !

[negbc]

There wasn't an exact solution as the dynamic source code doesn't work with automatic refreshes, so wouldn't work for my solution. However, you could still try with a single source.

Create a variable first for the date, last week for example:

LW = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-7),"MMddyy")

Then in your source code, enter the variable, concatenated to the file name.

= Csv.Document(File.Contents("C:\Users\NAME\Desktop\DATABASE\DATA123\"123OTB."& LW &".txt"),[Delimiter=" ", Columns=19, Encoding=1200, QuoteStyle=QuoteStyle.None])

I ended up connecting an entire folder and then selected the files I needed used this dynamic code, that way the refresh is the entire folder instead of a single file.

[dufoq3]

Hi @abc44 & @negbc. It is possible to do it the way above, but I would use Folder.Files and filter newest file.

1.) Use Folder as source

2.) Click Transform Data

3.) Sort by Date Crated (descending order) if necessary

4.) Click at 1st Binary in [Content] column

5.) Edit Navigation step and replace selected with 0 so the code for this step will be:

= Source{0}[Content]

This will choose 1st file dynamicaly.