How to find 7 years old files, put their details in excel, and email the results

Step by step flow that locates files older than 7 years and emails these to the user

Building this flow involves gathering the files, filtering them by date, formatting that data into a spreadsheet, and emailing it.

To make this robust and easy to build, we will generate a CSV (Comma Separated Values) file. Power Automate creates CSV files instantly and dynamically, whereas creating a native .xlsx file from scratch requires complex templating and pre-made tables. A CSV file opens natively in Excel and provides the exact same rows-and-columns result.

Why would you need this?
Typically, a flow like this would be most useful when doing document retention disposition activities. The disposition flow in Purview is slow by design (low priority), and it is useful to find out how many files are in the conveyor belt which may need drastic steps like custom PowerShell scripts if very large.

Here is the step-by-step guide to building this flow:

Objective

Since you are auditing old files, this flow should run on a schedule rather than being triggered by an event.

Go to Power Automate and click Create > Scheduled cloud flow.
Name the flow (e.g., "7-Year Old File Audit").
Set the starting date and time.
Set the frequency (e.g., 1 [Month] to run monthly).
Click Create.

Create a Scheduled Cloud Flow (Set the trigger)

We need to tell Power Automate exactly what date was 7 years ago today.

Click New step and search for Compose (Data Operation).
Click inside the Inputs field.
In the dynamic content menu that pops up, switch to the Expression tab.
Paste the following formula: addDays(utcNow(), -2557, 'yyyy-MM-dd')
(Note: 2,557 days accounts for 7 years plus roughly two leap years).
Click OK or Add to save the expression.

Calculate the 7-Year Cutoff Date

Next, we grab the file data from SharePoint, filtering out anything newer than 7 years.

Add a new step and search for the SharePoint action Get files (properties only).
Select your Site Address and Library Name from the dropdowns.
Click Show advanced options (or expand the action if using the new designer).
In the Filter Query field, type: Modified lt '
While your cursor is right after that single quote, use the dynamic content menu to select the Outputs from your Compose step.
Add a closing single quote ' immediately after the dynamic content.
Your final Filter Query should look like this: Modified lt '@{outputs('Compose')}'

Retrieve the SharePoint Files

The SharePoint action returns a massive amount of metadata. We only want a few clean columns for our Excel report.

Add a new step and search for Select (Data Operation).
In the From field, choose value (List of items) from the Get files (properties only) dynamic content.
In the Map section, create your column headers on the left and assign the dynamic content on the right:
Left side: File Name | Right side: Name (dynamic content)
Left side: File Path | Right side: Full Path (dynamic content)
Left side: Last Modified | Right side: Modified (dynamic content)
Left side: Modified By | Right side: Editor DisplayName (dynamic content)

Extract the Relevant Data

Now we convert that clean data into a spreadsheet format.

Add a new step and search for Create CSV table (Data Operation).
In the From field, insert the Output from the Select step.

Create the Spreadsheet

Finally, we send the generated table as an email attachment.

Add a new step and search for Send an email (V2) (Office 365 Outlook).
Fill out the To, Subject, and Body fields as desired.
Click Show advanced options.
In the Attachments Name - 1 field, type OldFilesReport.csv (Make sure you include the .csv extension).
In the Attachments Content - 1 field, insert the Output from the Create CSV table step.

Email the Report

You can get the same type or report by running the Content Search report, but this report will be more rigid, contain more useless column data and is harder to customise.
The file can be saved rather than emailed. Just switch out the last step.
The flow can be run as an automated flow to trigger monthly, quarterly or annually, depending on when you conduct retention policy checks.

How to find 7 years old files, put their details in excel, and email the results

Building this flow involves gathering the files, filtering them by date, formatting that data into a spreadsheet, and emailing it.

Here is the step-by-step guide to building this flow:

Objective

Since you are auditing old files, this flow should run on a schedule rather than being triggered by an event.

Go to Power Automate and click Create > Scheduled cloud flow.

Name the flow (e.g., "7-Year Old File Audit").

Set the starting date and time.

Set the frequency (e.g., 1 [Month] to run monthly).

Click Create.

Create a Scheduled Cloud Flow (Set the trigger)

We need to tell Power Automate exactly what date was 7 years ago today.

Click New step and search for Compose (Data Operation).

Click inside the Inputs field.

In the dynamic content menu that pops up, switch to the Expression tab.

Paste the following formula: addDays(utcNow(), -2557, 'yyyy-MM-dd')

(Note: 2,557 days accounts for 7 years plus roughly two leap years).

Click OK or Add to save the expression.

Calculate the 7-Year Cutoff Date

Next, we grab the file data from SharePoint, filtering out anything newer than 7 years.

Add a new step and search for the SharePoint action Get files (properties only).

Select your Site Address and Library Name from the dropdowns.

Click Show advanced options (or expand the action if using the new designer).

In the Filter Query field, type: Modified lt '

While your cursor is right after that single quote, use the dynamic content menu to select the Outputs from your Compose step.

Add a closing single quote ' immediately after the dynamic content.

Your final Filter Query should look like this: Modified lt '@{outputs('Compose')}'

Retrieve the SharePoint Files

The SharePoint action returns a massive amount of metadata. We only want a few clean columns for our Excel report.

Add a new step and search for Select (Data Operation).

In the From field, choose value (List of items) from the Get files (properties only) dynamic content.

In the Map section, create your column headers on the left and assign the dynamic content on the right:

Left side: File Name | Right side: Name (dynamic content)

Left side: File Path | Right side: Full Path (dynamic content)

Left side: Last Modified | Right side: Modified (dynamic content)

Left side: Modified By | Right side: Editor DisplayName (dynamic content)

Extract the Relevant Data

Now we convert that clean data into a spreadsheet format.

Add a new step and search for Create CSV table (Data Operation).

In the From field, insert the Output from the Select step.

Create the Spreadsheet

Finally, we send the generated table as an email attachment.

Add a new step and search for Send an email (V2) (Office 365 Outlook).

Fill out the To, Subject, and Body fields as desired.

Click Show advanced options.

In the Attachments Name - 1 field, type OldFilesReport.csv (Make sure you include the .csv extension).

In the Attachments Content - 1 field, insert the Output from the Create CSV table step.

Email the Report

You can get the same type or report by running the Content Search report, but this report will be more rigid, contain more useless column data and is harder to customise.

The file can be saved rather than emailed. Just switch out the last step.

The flow can be run as an automated flow to trigger monthly, quarterly or annually, depending on when you conduct retention policy checks.

Additional Notes

M365 Simplified