Forum Discussion
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}})
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"
1 Reply
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"