Automating Lists Snapshot using Power Automate: Part 1 – Snapshot

Automating Lists Snapshot using Power Automate: Part 1 – Snapshot

With a database in Microsoft Lists, capturing regular snapshots allows you to track changes over time.
This post will walk you through how to take snapshots of Microsoft Lists using Power Automate and implement them in Power BI.

Step 1: Access Power Automate

1. Sign in to your Microsoft account.
2. Click the Apps button in the top left corner.
3. Select Power Automate.

     

 

Step 2: Create a Scheduled Cloud Flow

1. In the left menu pane, click Create. Select Scheduled Cloud Flow.

2. Enter a Flow Name and set the Repeating Frequency as required.

3. The default action Recurrence is set. To modify it:
   • Click on the Recurrence action.
   • Adjust the configuration in the menu that appears on the left.
   • Set the Time Zone, if necessary.

 

Step 3: Retrieve List Data from SharePoint

1. Add a new action below the Recurrence action.

2. Search for Get items under the SharePoint section and select it.
3. Configure the following parameters:
   • Site Address: Enter the address of the SharePoint site where the Microsoft List is stored.
   • List Name: Select the list to snapshot.

Step 4: Convert List Data to CSV Format

1. Add another action below the Get items action.
2. Search for Create CSV table under Data Operations and select it.

3. Configure the parameters:
   1. From: Click the lightning icon and select Get items – body/value.
4. (Optional) Use the Advanced Parameters to specify columns to include in the snapshot.

 

Step 5: Create and Store the CSV File

1. Add another action below the Create CSV table action.
2. Search for Create file under the SharePoint section and select it.

3. Configure the parameters:

   

4. Site Address: Enter the SharePoint site where the file will be stored.
   • Folder Path: Select the folder where the snapshots will be saved.

     

   • File Name: Click the function button (fx) and enter:
     • concat(utcNow(),’_Project_Input’,’.csv’)
     • This ensures the file is named with the timestamp and a descriptive label.
   • File Content: Select Create CSV table – Output.
     
     
     

 

Step 6: Save and Test the Flow

1. Click Save at the top right corner.
2. Click Test, then select Manually.
3. Click Test and then Run Flow.
4. Once the flow starts successfully, click Done.
    
5. Verify that the snapshot file is saved in the configured folder.

 

Summary

Using Power Automate, we captured snapshots of Microsoft Lists on a set schedule. In this post, we saved the snapshot data in Excel files, but the storage format can vary depending on your needs.

 

Continued in Part 2 – Integration with Power BI

With the snapshot files in the Sharepoint storage, now we can load these files in Power BI and build visualizations for analysis.