cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brad_Vail
Frequent Visitor

Load the latest spreadsheet from a SharePoint folder

My business group will save monthly spreadsheets into a single SharePoint folder. I want the Query editor to open the newest file in the folder regardless of the file name. I have included a portion of the query and you can see that the name of the file that I opened in the first sequence is hard coded in the script. I want to sort the list to place the newest file at the top of the list. The sequence of steps recorded in the Query Editor hard codes the name of the file. When the newest file in the folder does not have the same name, the Query fails. How can I eliminate the steps that hard code the name of the file ?

 

 

Source = SharePoint.Contents("https://DOMAIN.sharepoint.com/sites/BUSINESS FOLDER/", [ApiVersion = 15]),
#"PATH LEVEL 1" = Source{[Name="PATH LEVEL 1"]}[Content],
PATH LEVEL 2 = #"PATH LEVEL 1"{[Name="PATH LEVEL 2"]}[Content],
PATH LEVEL 3 = PATH LEVEL 2{[Name="PATH LEVEL 3"]}[Content],

#"Account Transactions - ACCOU_Sheet" = #"Imported Excel Workbook"{[Item="Sheet 1",Kind="Sheet"]}[Data],
#"SPREADSHEET FILE XLSX" = PATH LEVEL 3{[Name="SPREADSHEET FILE.XLSX"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"SPREADSHEET FILE XLSX"),

#"Promoted Headers" = Table.PromoteHeaders(#"Sheet 1", [PromoteAllScalars=true]),
#"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Date", Order.Descending}})

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You should be able to filter [Date modified] equal to the maximal date modified and then take the first (index 0) row from that filtered table.

 

For example, this gives me the most recently modified .xlsx file from the site:

let
    Source = SharePoint.Files("https://DOMAIN.sharepoint.com/sites/SITENAME", [ApiVersion = 15]),
    #"Filtered .xlsx" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    #"Filtered last modified" = Table.SelectRows(#"Filtered .xlsx", each ([Date modified] = List.Max(#"Filtered .xlsx"[Date modified]))){0}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"Filtered last modified")
in
    #"Imported Excel Workbook"

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

You should be able to filter [Date modified] equal to the maximal date modified and then take the first (index 0) row from that filtered table.

 

For example, this gives me the most recently modified .xlsx file from the site:

let
    Source = SharePoint.Files("https://DOMAIN.sharepoint.com/sites/SITENAME", [ApiVersion = 15]),
    #"Filtered .xlsx" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    #"Filtered last modified" = Table.SelectRows(#"Filtered .xlsx", each ([Date modified] = List.Max(#"Filtered .xlsx"[Date modified]))){0}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"Filtered last modified")
in
    #"Imported Excel Workbook"

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors