Creating and Populating an Excel File in Power Automate

Overview

• Demonstration on creating and populating a new Excel file using Power Automate.
• Scenarios include retrieving data from an API or filtering an existing Excel file to create a fresh one.

Steps for Creating an Excel File

Step 1: Create a New Excel Workbook

• Go to the document library and create a new Excel workbook.
• Close the workbook without making changes to prepare for the flow.

Step 2: Set Up Flow with Manual Trigger

• Start a new flow with a manual trigger.
• Use the Get file content action to select the SharePoint site and the new file.
• Save and test the flow to capture the output and ensure future robustness against accidental file deletion.

Step 3: Capture File Content

• Access the flow history and copy the file content output.
• Create a Compose action with the copied file content for future use.
• Delete the Get file content action and the temporary blank file from SharePoint.

Step 4: Create a New Excel File

• Use Create file action to create a new file in the same SharePoint site.
• Naming convention: Format date and time in UTC to create a unique filename (year-month-day-hours-minutes-seconds) with .xlsx extension.
• Assign content from the Compose action to this new file.

Step 5: Create a Table in Excel File

• Use Create table action with the following parameters:
  • Location: Demobile 365
  • Document Library: My Test Lib
  • File: Use the output ID from the Create file step.
  • Table Range: A1:E1 for 5 columns (A, B, C, D, E).
  • Table Name: My New Table
  • Column Names: Name, Age, Address 1, Address 2, Postcode.

Step 6: Add Row(s) to the Table

• Start with adding a single row to the table. Example values:
  • Name: Damian
  • Age: 40
  • Address 1: A house somewhere
  • Address 2: Scotland
  • Postcode: AB10 1AB.

Step 7: Test the Flow

• Test the flow to ensure it runs successfully and creates the Excel file with the specified data.

Populating the Excel File with Multiple Records

Step 8: Edit Flow for External Data Source

• Prepare a JSON array of 50 records (names, ages, addresses).
• Add a Compose action to insert this array into the flow.

Step 9: Implement Looping

• Add Apply to each loop to iterate over the JSON array.
• Move the Add a row action into the loop.
• Remove static values from the row input and replace them with expressions referencing the array items:
  • Example Syntax: item()?['name'], item()?['age'], etc.

Step 10: Adjust Concurrency Settings

• Increase the concurrency of the loop to handle multiple additions in parallel (set to 50).
• Close the Excel file and test the updated flow.

Final Note

• All 50 rows should populate correctly in the new Excel file upon successful execution.

Conclusion

• The focus is on effectively using Power Automate to create and populate Excel files.
• This method can be applied to various scenarios involving external data sources (like APIs) or other Excel files.
• Encourage viewer engagement through likes, subscriptions, and comments for further inquiries or topics to explore.