Hi folks.
I'm not sure if this can be done or not.
I have multiple sheets based on monthly data.
Col A on each sheet has the same Course IDs with each FY Month, Apr - March across the top.
Each time I drop a new month in, it just appends the Course IDs to the bottom of the last one. So I end up with 12 of each course IDs at the end of the year. Is there a way that when we drop a new month in that it merges data? We need all the data in one table to feed another sheet.
If not, is there a way to take the latest sheet only? As that will have the previous moths also.
We could just replace the last one with a new one with the same Filename but we'd like to identify an individual month should an error occur with the data.
Scratching my head - I may be overthinking this!
Thanks
Indeed, connecting to a folder will combine all files by appending them, i.e. stacking them on top of each other. Now, I understand that the last file is always the one you want to connect to. Not any of the older ones.
Then, in the window (that gives a view of the entire folder), press Transform Data. And I assume that the folder only contains relevant CSV files.
Then there are three date columns and you probably want to focus on Date Created. Select that column and sort it in descending order (newest to oldest). Keep top 1 row. That's your latest file. Now expand its content.
If that doesn't solve your problem, please elaborate.
Thanks Riny, thought that would be the case.
With regard to just showing the latest, is there a way of ignoring the older files using the date created when the newest one is appended?
Thanks
Andy
Got it!
For anyone else - this selects only the top (latest) file in the list. I have it based on the filename being in descending order (Just numbered 12 to 1)
1. Upload from folder as normal
2. Select Name > Select Z-A
3. Click fx> #"Sorted Rows"
4. Add: [Content]{0} so it looks like: #"Sorted Rows"[Content]{0}
4. Click off.
