Forum Discussion
Load most recent file from SharePoint folder
I would like to load the most recent CSV file from a SharePoint folder.
i tried using the method below but it seems the file and path is hardcoded.
let
Source = SharePoint.Files("https://musux.sharepoint.com/sites/poc", [ApiVersion=15]),
#"Filtered Rows" = Table.SelectRows(#"Source", each Text.StartsWith([Folder Path], "https://musux.sharepoint.com/sites/poc/Doc")),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Top Row Table" = Table.FirstN(#"Sorted Rows1",1),
OpenBin = #"Top Row Table"{[Name="Report_1533371.csv",#"Folder Path"="https://musux.sharepoint.com/sites/poc/Doc"]}[Content],
#"Imported CSV" = Csv.Document(OpenBin,[Delimiter=",", Columns=45, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
#"Imported CSV"
How do change the code so that it takes the name and path from the "Top Row Table" ?
Remember you can address rows and columns in Power Query. Here is an example to open the newest Excel file. You can adjust it for csv. Note there is no need for the "keep only first row" transform.
let Source = SharePoint.Files("https://your.sharepoint.com/sites/yoursite", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")), #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}), #"First File" = #"Sorted Rows"{0}[Content], #"Imported Excel Workbook" = Excel.Workbook(#"First File") in #"Imported Excel Workbook"
2 Replies
Remember you can address rows and columns in Power Query. Here is an example to open the newest Excel file. You can adjust it for csv. Note there is no need for the "keep only first row" transform.
let Source = SharePoint.Files("https://your.sharepoint.com/sites/yoursite", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")), #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}), #"First File" = #"Sorted Rows"{0}[Content], #"Imported Excel Workbook" = Excel.Workbook(#"First File") in #"Imported Excel Workbook"Thanks. I am new to M and did not know about the syntax for addressing row and column!
How do i store the file name in another table so that I can display the name of the file ?
