Forum Discussion
Filter Old Draft Folders
The grouping is currently set at the Draft folder level, which is why you’re seeing one file per Draft instead of one per PR folder.
You should group at the PR-26*** level so all Draft folders are considered together. Then, use the maximum modified date within each group and filter to that row. This will give you only the most recent file across all Draft folders for each PR.
If you still get multiple rows, sort within each group and keep just the top row to ensure a single result.
Please try this approach and let us know how it works for you.
Should be possible with Power Query but it may not be fast. How many files overall are in that folder structure?
Also note that "Last Modified" is a slippery slope. Very easy to get false positives on that.
The amount of files is really dependent on how many drafts we end up with. On average I would say propably 5 files counting everything from the PR-26*** folder down. I don't really need it to be fast.
Connect to the Sharepoint site via "Sharepoint Folders", click "Transform data" *
Filter for the desired file name patterns (and folder names if needed)
Sort by Last Modified Date descending
Keep the top 1 row.
*) This will give you all the files in all the folders on that sharepoint site.
I think maybe explanation of what the final result should be was confusing. Keep top row 1 gives me only the most recent file from the entire PR-26*** list of files. I want the most recent file from the group of "Draft" folders in each PR-26*** folder with the file path to each file. Also I should mention there is no SharePoint involved, so this would all be "from file - from folder".
