←Back to Beyond Market Intelligence
•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Filter only most recent value in each day from a query

Our take

If you're working with a query that pulls multiple daily exchange rates but only need the most recent value for each day, Power Query offers an efficient solution. By leveraging its capabilities, you can filter your data to retain only the latest exchange rate for every date. This approach eliminates the need for manual CSV downloads and cumbersome formatting adjustments. Instead, you can streamline your workflow, ensuring that your data remains consistently updated and easily integrated into your spreadsheets.

I 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]

Read on the original site

Open the publisher's page for the full experience

View original article →

Tagged with

#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#real-time data collaboration#big data management in spreadsheets#financial modeling with spreadsheets#real-time collaboration#rows.com#AI-native spreadsheets#conversational data analysis#Excel compatibility#intelligent data visualization#predictive analytics in spreadsheets#cloud-native spreadsheets#data visualization tools#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions