Power Query Source File Name Change to Current Date

[Joey_Ng]

Hi,

I have a file saved on Sharepoint and updated daily with the filename ending with YYYYMMDD. For example:
= Excel.Workbook(Web.Contents("https://sharepoint.com/teams/Test Report 20220228.xlsx"), null, true)

I would like to be able to amend the above code so everyday when I open the file the source filename updates to the current date. I searched for DateTime.LocalNow() but unsure how to incorporate the code so it shows /Test Report YYYYMMDD.xlsx at the end. If the code can include the "space" after the word Report too that would be much appreciated.

Many Thanks,

Joey

 

[JGordon11]

try

let
    YYYYMMDD = Date.ToText(Date.From(DateTime.LocalNow()), [Format = "YYYYMMDD"]),
    filename = "https://sharepoint.com/teams/Test Report " & YYYYMMDD & ".xlsx",
    Source = Excel.Workbook(Web.Contents(filename), null, true)
in
    Source

 

[Joey_Ng]

try

let
    YYYYMMDD = Date.ToText(Date.From(DateTime.LocalNow()), [Format = "YYYYMMDD"]),
    filename = "https://sharepoint.com/teams/Test Report " & YYYYMMDD & ".xlsx",
    Source = Excel.Workbook(Web.Contents(filename), null, true)
in
    Source

Thank You for this. By declaring the YYYYDDMM as text, I was able to use it directly inside the link.

 

[ConfusedatWork2]

try

let
    YYYYMMDD = Date.ToText(Date.From(DateTime.LocalNow()), [Format = "YYYYMMDD"]),
    filename = "https://sharepoint.com/teams/Test Report " & YYYYMMDD & ".xlsx",
    Source = Excel.Workbook(Web.Contents(filename), null, true)
in
    Source

What if I don't know what date it could be? For example it could be three days ago, or 1 day ago. And my date format is DDMMMYYYY. I just want the file, no matter what the date is.

 

[jdellasala]

Create a cell with the text you want as well as the full path and give that cell a name:

Book1
	A	B
1	Today's File:	https://sharepoint.com/teams/Test Report 20221219.xlsx
Sheet1

Cell Formulas
Range	Formula
B1	B1	="https://sharepoint.com/teams/Test Report "&TEXT(TODAY(),"yyyymmdd")&".xlsx"

Cell B1 above has the Named Range of TodaysFile. That is brought into PQ and reduced to just the text (using Drill Down):

let
    Source = Excel.CurrentWorkbook(){[Name="TodaysFile"]}[Content],
    Column1 = Source[Column1],
    Column2 = Column1{0}
in
    Column2

That query is named TodaysFile. Then use it as the parameter of your source:

let
    Source =  Excel.Workbook(Web.Contents(TodaysFile)),
.....

Refresh All should update the URL and bring in the desired file (but double check until you're sure!).
That said, I actually like JGordon11's solution better! Basically the same thing with a little different approach.