How to Import the Newest CSV from SharePoint using Power Query
Question details
The user needs a way to automatically import the most recently uploaded CSV file from a SharePoint folder, avoiding the need to manually update the query every time the file name changes due to new date and time stamps.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating recurring data imports from a SharePoint folder where daily or weekly CSV reports are saved with dynamic time-stamped filenames.
- Observed behavior
- The goal is to dynamically locate, filter, and extract the latest CSV file based on its modification date attribute rather than hardcoding the query to look for a specific file name.
Ensure you have the correct SharePoint root URL and that your account has the necessary permissions to read the contents of the target SharePoint folder.
Filter and Sort Files by Date Modified in Power Query
This solution uses Power Query's built-in transformations to retrieve all files from the SharePoint directory, filter out irrelevant files, sort them by their modification date, and extract data only from the newest one.
By utilizing the SharePoint.Files function, Power Query can read metadata (like Date modified) alongside the file contents. You can isolate the correct file type using text filters before applying a descending sort to grab the latest record.
Open Excel, navigate to the Data tab, select 'Get Data', choose 'From File', and then 'From SharePoint Folder'. Enter your SharePoint site URL and click OK.
When the preview window appears, do not click Load. Instead, click 'Transform Data' to open the Power Query Editor where you can manipulate the file list.
In the Power Query Editor, locate the 'Name' column. Click the filter dropdown, choose 'Text Filters', and use 'Begins With' or 'Contains' to isolate your target CSV files from other documents in the folder.
Find the 'Date modified' column, click the dropdown arrow in the column header, and select 'Sort Descending'. This action ensures the newest file appears at the very top of your table.
On the Home tab of the Power Query ribbon, click 'Keep Rows', then 'Keep Top Rows'. Enter '1' in the prompt and click OK to retain only the most recent file.
Locate the 'Content' column and click the 'Combine Files' icon (two downward arrows). Power Query will extract the CSV data. Once processed, use 'Use First Row as Headers' if needed, and finally click 'Close & Load' to bring the data into Excel.
Try WPS Office for Fast and Efficient Data Management
If you need a lightweight, cost-effective alternative to Microsoft Office for managing complex datasets, importing CSVs, and executing daily spreadsheet tasks, WPS Office is an excellent choice. It provides a highly compatible and familiar workspace without the heavy subscription fees.
Frequently Asked Questions
Can I filter files by a specific subfolder path within SharePoint?
Yes. Before sorting by the modification date in the Power Query Editor, locate the 'Folder Path' column. Filter this column to match the exact SharePoint subfolder path where your CSV files are deposited to avoid pulling files from unrelated directories.
What if my reports are saved as Excel workbooks (.xlsx) instead of CSV files?
The initial steps remain exactly the same. You filter the files and sort by 'Date modified' to keep the top row. The only difference is that when you click the 'Combine Files' icon, Power Query will prompt you to select the specific worksheet or table name from the Excel workbook to extract.
Why is Power Query showing an authentication error when connecting to SharePoint?
This usually happens when the wrong credential type is used. When connecting to a SharePoint folder for the first time, make sure you select 'Organizational account' on the left side of the credentials window and click 'Sign In' using your Microsoft 365 account, rather than using Anonymous or Windows authentication.
