Power Query Source File Name Change to Current Date

Joey_Ng

New Member
Joined
Mar 7, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
try

Power Query:
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
 
Upvote 0
try

Power Query:
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.
 
Upvote 0
try

Power Query:
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.
 
Upvote 0
Create a cell with the text you want as well as the full path and give that cell a name:
Cell Formulas
RangeFormula
B1B1="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):
Power Query:
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:
Power Query:
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.
 
Upvote 0

Forum statistics

Threads
1,223,398
Messages
6,171,887
Members
452,429
Latest member
simransonu08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top