Get Data from SharePoint Folder with Today's Date

[sinanalmac]

Hi All

I need PowerQuery to go every day and  get  file that is stored in a sharepoint folder with today's date.

Explanation:

Our raw data (the one we feed to PowerQuery) are stored to our sharepoint foller ; this is an example of the path:
https://brbsapp266.sharepoint.com/sites/DaliyDisputeReport/Shared Documents/General/
file like this. 

As you can see,  every day the new report is stored in a folder with the diffrent date suffix of when the report is extracted.

• The timestamp on the file is when the file itself was created.

I can change the names of the files or the date format of the suffix.
For exaple like these: 
DailyDisputeReport_31_03_2022.xlsx
DailyDisputeReport_31032022.xlsx
DailyDisputeReport_31-03-2022.xlsx

Thank you  and Best Regards
Sinan 

B

[sinanalmac · Accepted solution]

Hİ  ,all  I solved The problem. 

I changed some  M Language  in advanced Editor like this

let
    Source = SharePoint.Files("https://unilever.sharepoint.com/sites/DaliyDisputeReport/", [ApiVersion = 15]),
    #"Daily-Dispute-Report_14 06 2022 xls_https://brbsapp266 sharepoint com/sites/DaliyDisputeReport/Shared Documents/General/" = Source{[Name="Daily-Dispute-Report_"&Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),"dd.MM.yyyy")&".xls",#"Folder Path"="https://brbsapp266.sharepoint.com/sites/DaliyDisputeReport/Shared Documents/General/"]}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"Daily-Dispute-Report_14 06 2022 xls_https://brbsapp266 sharepoint com/sites/DaliyDisputeReport/Shared Documents/General/"),
    #"Crm List_Sheet" = #"Imported Excel Workbook"{[Item="Crm List",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Crm List_Sheet", [PromoteAllScalars=true])
    
in
 

Thank you 

[BA_Pete · Accepted solution]

Hi @sinanalmac ,

Connect to your SharePoint folder using the SharePoint Folder connector.

When the files list comes up in the connection dialog, hit the Transform button. This should give you a list of all files available from that folder, something like this:

Filter this table as you would a normal table, using a combination of filters on [Name], [Date modified], and [Date created]  to create a generic set of filters that will always select 'today's' file from your folder.

For example, your filter arguments may look something like this:

Table.SelectRows(
    previousStepName,
    each Text.StartsWith([Name], "DailyDispute")
        and [Date created] = Date.From(DateTime.LocalNow())
)

Once you've narrowed your table down to the one report for today, click the yellow "Binary" word in the [Content] column to open up that file in your query.

Pete

[sinanalmac]

Hi @BA_Pete ,
Thank you  for your  help. You're right.  your solution is more logical then mine. I will  Accept your reply as solution  as well. 
best regrads

[sinanalmac · Accepted solution]

Hİ  ,all  I solved The problem. 

I changed some  M Language  in advanced Editor like this

let
    Source = SharePoint.Files("https://unilever.sharepoint.com/sites/DaliyDisputeReport/", [ApiVersion = 15]),
    #"Daily-Dispute-Report_14 06 2022 xls_https://brbsapp266 sharepoint com/sites/DaliyDisputeReport/Shared Documents/General/" = Source{[Name="Daily-Dispute-Report_"&Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),"dd.MM.yyyy")&".xls",#"Folder Path"="https://brbsapp266.sharepoint.com/sites/DaliyDisputeReport/Shared Documents/General/"]}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"Daily-Dispute-Report_14 06 2022 xls_https://brbsapp266 sharepoint com/sites/DaliyDisputeReport/Shared Documents/General/"),
    #"Crm List_Sheet" = #"Imported Excel Workbook"{[Item="Crm List",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Crm List_Sheet", [PromoteAllScalars=true])
    
in
 

Thank you 

[BA_Pete]

Wow, bad timing for my answer! 😂

I'd say that you might need to be careful with your solution if your file names are created manually by humans. In this case, there's a real chance that someone could mistype the date on the file name. With my solution, you're always picking up the file that was created today, regardless of how the date appears on the file name.

Pete