Hello there,
Firstly can I thank Mynda for the extraordinarily helpful videos she records on YouTube. I consider myself an intermediate-advanced user of Excel and her videos have been most helpful in getting me out of a couple of rabbit holes and help me learn a few tricks along the way. I'm a beginner to PowerQuery, again, mostly self teaching through Mynda's videos. When expenses allow, it's a priority for me to purchase the PowerQuery course.
I attach a sample spreadsheet of what I'm looking to achieve. Essentially, I want to create an additional column that returns the latest date for each FileName through PowerQuery.
I've got as far as using the 'Group By' feature, but then I get lost. The Group By feature works to an extent, but it condenses the rows and columns like a PivotTable. I want to retain all the columns and rows. Doesn't feel like a hard task and I'll probably feel stupid when I see the answer. 🙂
Sincerest thanks for any help you can provide. (Edit: Sorry for the image formatting!)
Hi Peter,
Welcome to our forum.
Try this:
Create a separate query with the initial list of files, sort the DateTime descending, then remove the duplicates with this column selected. You will get the list with only the latest date for each file.
You can merge it now with your query, File Name will be the key for matching, then expand the DateTime from the merged column.
