•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Power Query - select latest file
Our take
Are you tired of manually updating your data with outdated files? With Power Query, you can easily automate the ingestion of the latest CSV from your SharePoint folder, ensuring you always work with the most current data snapshot. Instead of hardcoding file names, consider leveraging dynamic file references that adapt to the latest timestamp. This method streamlines your workflow and enhances productivity, eliminating the hassle of searching for the right file. Let’s explore the steps to make this transformation seamless and efficient.
Hi!
I’m grappling with the following structure:
Twice per day, at 7:30 and at 14:00, an exported csv file is added to a sharepoint folder. The csv shows a snapshot of the current state of system data, so there’s no need for all of the csvs to be ingested. I need to always ingest the latest file. The naming convention is “export yyyy-mm-dd hh-mm-ss”.
I tried using sharepoint folder as my connector, navigating to the specific subfolder, sort by date added, keep only top row. So far so good, but the next step in the query hardcodes the file name. The next version of the file will have a different timestamp, so the hardcoded name doesn’t correspond to the latest file anymore.
Any other approach come to mind?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Filter only most recent value in each day from a queryI have a query that pulls exchange rates. Unfortunately the data available contains all of the daily currency exchange rate reports (4 or 5 per day) but I only need the latest one for each day. Snapshot below shows column with Date and Time (dd/mm/yyyy HH:MM:SS) and the exchange rate. For example, for 02/01/2020 I only want to keep the latest value, from time 13:11:11. Notice that this is in PowerQuery, and I would rather resolve it here, since I pull this query into different spreadsheets. Maybe a Pivot Table and filter could do it in a table, but then I can't make a query into that PivotTable. My current workaround is to download a CSV from another source, but I have to manually download it and manually replace colons, commas etc. to match the desired format. STOCKHISTORY won't do it, because (a) it's not the official rate, and (b) because the query and the CSV download are alway automatically updated to the latest date. Thanks for any suggestions. Snapshot submitted by /u/CosmoCafe777 [link] [comments]
- I want to use Power Query to import data received from a client, where the file name changes each month. What's the easiest way to automate this?I used to use VBA for this, but that's a lot more roundabout, and I have a lot less control over the transformation. I have no issues with transforming the actual data itself. My issue lies in the fact that it's a different file each month. Using wildcard formatting, *filehere*.xls* would always pull the correct file. This file is also stored in the same place relative to my spreadsheet each time, but the location of the spreadsheet and folders itself changes each month. In VBA, I could find the relative position quite easily via ThisWorkbook.Path & "\Data\" However, I don't know how to use PQ to import automatically like this, so that I'd always import the correct data simply by refreshing links. I think I've seen people set up a somewhat hacky way, where PQ first reads a table in the workbook to retrieve values, and then uses those to find the file to query. Is that the only way? submitted by /u/space_reserved [link] [comments]
- Power Query: my source doesn't always contain the same columns. How do you handle this?Hi all. I'm producing reporting based on data from our CRM. They're using Looker. My issue is, Looker seems to only generate a field if there's data for it. So my data can include a field on one period, but it might not be present on the next - let's say if no items for Smartphones category are sold, the csv won't have a smartphones column. What's the best way to handle this so that I don't have to spend time every refresh to fix the queries? submitted by /u/m4492 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#rows.com#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#Excel compatibility#row zero#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#Power Query#latest file#csv file#sharepoint folder#data ingestion#naming convention#file name#timestamp#query