Help understanding query to add dynamic Date to file name

[Anonymous]

Hi all,

I'm very new to Power BI, so I hope I don't upset anyone here.

I'm trying to understand how to add a dynamic query to get a file as data source.

Most of the file name is constant, but a new file with the current date is generated every day and I want to target that updated file as the data source. Kind of a regex, right?

I googled and find out it's possible to use DateTime.Date in the query as part of the File name.

That should fix my problem.

However, I don't understand the query I generated and I don't know in where should I modify/add this.

This is my current query:

let
Source = AzureStorage.Blobs("storageaccountname"),
backupreportsxlsx1 = Source{[Name="containername"]}[Data],
#"https://storageaccountname.blob.core.windows.net/containername/_filename-14-11-19.xlsx" = containername1{[#"Folder Path"="https://storageaccountname.blob.core.windows.net/containername/",Name="filename-14-11-19.xlsx"]}[Con...],
#"Imported Excel" = Excel.Workbook(#"https://storageaccountname.blob.core.windows.net/containername/filename-14-11-19.xlsx"),
file_Table = #"Imported Excel"{[Item="file",Kind="Table"]}[Data]
in
file_Table

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following formula to meet your requirement, the filename will try to generate the name "filename-day-month-year.xlsx"  of current day when refresh.

let
Source = AzureStorage.Blobs("storageaccountname"),
backupreportsxlsx1 = Source{[Name="containername"]}[Data],
filename = "filename-" 
		& Number.ToText(Date.Day(DateTime.LocalNow()))  
		& "-" & Number.ToText(Date.Month(DateTime.LocalNow()))
		& "-" & Number.ToText(Date.Year(DateTime.LocalNow()))
		& ".xlsx"
#"FileLocation" = containername1{[#"Folder Path"="https://storageaccountname.blob.core.windows.net/containername/",Name=filename}[Con...],
#"Imported Excel" = Excel.Workbook(#"FileLocation"),
file_Table = #"Imported Excel"{[Item="file",Kind="Table"]}[Data]
in
file_Table

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the help!