Power Automate Desktop - Excel Consolidation and Summary Automation

PAD

Overall Goal

You are automating a process to:

  • Collect all Excel files (.xlsx) from a folder (including subfolders).
  • Combine their data into one master table.
  • Calculate total and average sales by region (North, South, East, West).
  • Save everything in a new consolidated Excel file with two sheets:
    • One sheet with all raw data
    • Another sheet with summarized regional totals and averages
    • Download the sample files from the below link
    • Power-Automate-Desktop Samples

Power Automate Desktop – Pseudo Code Explanation

This Power Automate Desktop (PAD) pseudo code automates the process of consolidating Excel files and calculating total and average sales by region. Below is the plain English explanation of each step and why it’s performed:

1. Get all Excel files: The flow searches through the folder E:\Power Platform\PAD\Samples (including subfolders) to collect every Excel file with the extension .xlsx.
Why: So that the process automatically finds and processes all relevant Excel files without manually locating them.

2. Create an empty data table: A blank data table is created with three columns - Region, Sales Person, and Total Sales.
Why: This acts as a master table that will store all the sales data from the different Excel files.

3. Loop through each Excel file: The flow loops through every file it found. For each file, it opens Excel, reads all the data (with headers), merges that data into the main table, and then closes the file. A counter variable keeps track of how many files are processed.
Why: This ensures that all data from multiple Excel files is combined into one consolidated dataset, and the counter helps later when calculating averages.

4. Process combined data by region: The flow goes through each row in the combined table and checks which region it belongs to (North, South, East, or West). It adds the sales values to the correct region’s total.
Why: This step calculates the total sales for each region so we can get regional performance metrics.

5. Create a summary data table: A new table is created showing each region’s total sales and average sales (calculated as total sales divided by the number of files processed).
Why: This summary provides an easy-to-read breakdown of total and average sales per region.

6. Check and create folder if missing: The flow checks whether the folder E:\Power Platform\PAD\Consolidated Data exists. If not, it creates it.
Why: To ensure the final consolidated file has a proper place to be saved.

7. Create a new Excel workbook: A new Excel instance is launched. The first sheet is renamed to Consolidated Data and contains all the merged raw data. Then, a new sheet named Calculation is added containing the summarized totals and averages.
Why: This creates a professional, well-structured report with both detailed and summarized information.

8. Add timestamp and save the file: The current date and time are captured and formatted (e.g., 2025_11_14 05_30_00). The workbook is saved with this timestamp in its filename (e.g., Consolidate Data_2025_11_14 05_30_00.xlsx).
Why: Each run saves a unique file with a timestamp, preventing overwriting and helping track when the report was generated.

Final Output:
- Folder: E:\Power Platform\PAD\Consolidated Data
- File: Consolidate Data_<timestamp>.xlsx
- Sheet 1: Consolidated Data (All raw data)
- Sheet 2: Calculation (Total and average sales by region)

In short, this PAD automation gathers Excel files, merges them into one dataset, calculates regional totals and averages, and saves everything neatly into a timestamped Excel file for easy tracking and reporting.

Pseudo Code

    Folder.GetFiles Folder: $'''E:\\Power Platform\\PAD\\Samples''' FileFilter: $'''*.xlsx''' IncludeSubfolders: True FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Variables.CreateNewDatatable InputTable: { ^['Region', 'Sales Person', 'Total Sales'] } DataTable=> DataTable
SET Counter TO 0
LOOP FOREACH CurrentFile IN Files
    Variables.IncreaseVariable Value: Counter IncrementValue: 1
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentFile.FullName Visible: True ReadOnly: False UseMachineLocale: False Instance=> ExcelInstance
    Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance GetCellContentsMode: Excel.GetCellContentsMode.TypedValues FirstLineIsHeader: True RangeValue=> ExcelData
    Variables.MergeDataTables FirstDataTable: DataTable SecondDataTable: ExcelData MergeMode: Variables.MergeMode.IgnoreExtraColumns
    Excel.CloseExcel.Close Instance: ExcelInstance
END
LOOP FOREACH CurrentItem IN DataTable
    SWITCH CurrentItem[0]
        CASE = $'''North'''
            Text.ToNumber Text: CurrentItem[2] Number=> NorthSales
            SET TotalNorthSales TO TotalNorthSales + NorthSales
        CASE = $'''South'''
            Text.ToNumber Text: CurrentItem[2] Number=> SouthSales
            SET TotalSouthSales TO TotalSouthSales + SouthSales
        CASE = $'''East'''
            Text.ToNumber Text: CurrentItem[2] Number=> EastSales
            SET TotalEastSales TO TotalEastSales + EastSales
        CASE = $'''West'''
            Text.ToNumber Text: CurrentItem[2] Number=> WestSales
            SET TotalWestSales TO TotalWestSales + WestSales
    END
END
Variables.CreateNewDatatable InputTable: { ^['Region', 'Total Sales', 'Average Sales'], [$'''North''', TotalNorthSales, TotalNorthSales / Counter], [$'''South''', TotalSouthSales, TotalSouthSales / Counter], [$'''East''', TotalEastSales, TotalEastSales / Counter], [$'''West''', TotalWestSales, TotalWestSales / Counter] } DataTable=> DataTable2
IF (Folder.IfFolderExists.DoesNotExist Path: $'''E:\\Power Platform\\PAD\\Consolidated Data''') THEN
    Folder.Create FolderPath: $'''E:\\Power Platform\\PAD''' FolderName: $'''Consolidated Data''' Folder=> NewFolder
END
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True UseMachineLocale: False Instance=> ExcelInstance2
Variables.CreateNewDatatable InputTable: { ^['Region', 'Sales Person', 'Total Sales'], [$'''Region''', $'''Sales Person''', $'''Total Sales'''] } DataTable=> ExcelHeaderTable1
Variables.CreateNewDatatable InputTable: { ^['Region', 'Sales Person', 'Average Sales'], [$'''Region''', $'''Sales Person''', $'''Average Sales'''] } DataTable=> ExcelHeaderDataTable2
Excel.RenameWorksheet.RenameWorksheetWithName Instance: ExcelInstance2 Name: $'''Sheet1''' NewName: $'''Consolidated Data'''
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance2 Name: $'''Consolidated Data'''
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: ExcelHeaderTable1 Column: $'''A''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: DataTable Column: $'''A''' Row: 2
Excel.AddWorksheet Instance: ExcelInstance2 Name: $'''Calculation''' WorksheetPosition: Excel.WorksheetPosition.Last
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: ExcelHeaderDataTable2 Column: $'''A''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: DataTable2 Column: $'''A''' Row: 2
DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: CurrentDateTime CustomFormat: $'''yyyy_MM_dd hh_mm_ss''' Result=> FormattedDateTime
Excel.CloseExcel.CloseAndSaveAs Instance: ExcelInstance2 DocumentFormat: Excel.ExcelFormat.OpenXmlWorkbook DocumentPath: $'''E:\\Power Platform\\PAD\\Consolidated Data\\Consolidate Data_%FormattedDateTime%.xlsx'''

Comments

Post a Comment

Popular posts from this blog

Part 1: Creating Code Apps in Power Apps - A step-by-step guide (with real errors I faced & how I fixed them)

Image
Intro In this post I’ll walk you through how I set up a code-based app for Power Apps (React/Vite front-end + Power Platform CLI for packaging/deployment), the exact errors I ran into, and how I solved them. If you want a hands-on, copy-paste able guide that covers Node versions, pac auth, environment issues, and packaging tips, this is for you. Prerequisites Microsoft 365 account with access to a Power Platform environment Node.js ( 20.19+  recommended LTS) VS Code Install power platform tools extension in VS Code. Steps: Step 1 : Open Visual Studio Code and launch the integrated terminal. Step 2: In the terminal, run the following commands one by one.   mkdir D:\CodeApps - Force cd D:\CodeApps npm create vite @latest myfirstapp -- -- template react - ts cd D:\CodeApps\myfirstapp npm install Initially, I ran the command: npm create vite@latest MyFirstApp -- --template react-ts This gave the error: Invalid package.json name The issue was caused because package...
Read more

Calling Microsoft Graph API from Power Automate Using Azure App Services – Step-by-Step Guide

Image
Step 1: You should have an Azure subscription, or if you have access to the Microsoft Entra Admin Center, you can create an app from there. This app can then be used in Power Automate to connect to Microsoft Graph API. Step 2: Click on "New registration" , provide a name for your app, assign the required API permissions (such as Microsoft Graph permissions), and then click "Register" to complete the app registration process. Step 3: After registration, you'll be redirected to the app's overview page. From there, navigate to the API permissions tab and click "Add a permission" to assign the necessary Microsoft Graph API permissions. Step 4: Select Microsoft Graph as the API, and based on your requirements, choose the appropriate permissions. In my case, I selected Application permissions , which do not require user sign-in and are suitable for background services or automation. Step 5: Select the required Microsoft Graph API permissions. Yo...
Read more

Step-by-Step Guide: Power Automate Custom Connector Using Graph API from Azure App Service

Image
Step 1: Open Power Automate, navigate to the Custom Connectors tab, and select Create from blank as shown in the image below. custom connector(click image for better resolution) Step 2: In the General tab, upload an icon (less than 1MB), add a description, select the correct scheme (in my case, HTTPS ), and provide the host name. For example, if your API URL is https://graph.microsoft.com/v1.0/users/{email} , the host name will be the highlighted part — graph.microsoft.com . Step 3: Go to the Security tab. Here, you need to select the authentication method. Choose OAuth 2.0 as the authentication type, as shown in the image below. Step 4: Select the Identity Provider as Azure Active Directory . Check the box Enable Service Principal Support . This option allows you to create a connection using a Client ID and Client Secret instead of using the user's own authentication. Next, provide the Client ID , Client Secret , Tenant ID , and other required details as shown in t...
Read more