Power Automate offers both cloud flows and desktop flows. Cloud flows are useful for connecting apps and services and automating business processes that can run from automated triggers or schedules within your Power Platform tenant. Desktop flows are designed for more localized, computer-based automation and day-to-day tasks. They can still interact with online services such as Dataverse, but you will usually activate desktop flows yourself. They can also provide data-processing capabilities that aren't available in cloud flows and are especially useful when working with legacy systems that cloud flows cannot easily access.

In this Fast Class, we build a desktop flow that:

• Takes Excel files from a folder selected by the user at runtime.
• Asks the user to specify a department.
• Finds rows associated with that department across multiple Excel files.
• Combines those rows into one data table.
• Creates a new Excel file containing the combined results.

Getting familiar with the desktop flow designer

The Power Automate desktop designer looks quite different from the cloud flow designer. A list of available actions appears on the left side of the designer, and you can search for actions when you know approximately what you need. You can also organize your flow into regions. Regions aren't required for a flow to work, but they let you group related actions together and collapse sections of a longer flow, making it easier to navigate. For this example, the flow is divided into logical sections for collecting user input, preparing the data, processing the source files, and creating the final Excel workbook.

Collecting user input

The first part of the flow collects information from the user at runtime. Using the Display select folder dialog action, the flow asks the user to select the folder containing the source Excel files. The selected folder is stored in a variable so it can be referenced later in the flow. The dialog also returns information about which button the user selected. This is important because selecting Cancel does not automatically cancel the entire desktop flow. Instead, the selected folder has a null value and the button result indicates that Cancel was selected. To handle this, the flow uses a condition:

• If the user selects Cancel, stop the flow.
• Otherwise, continue processing.

A second dialog collects the department that the user wants to find in the Excel data. A similar condition stops the flow if the user cancels this dialog.

Creating a data table

Next, the flow creates a data table that will eventually contain all matching records from the source Excel files. A data table is a variable designed to hold tabular information. In this example, the table contains the columns needed for the expense data, including fields such as:

• Expense ID
• Date
• Department
• Category
• Amount

As the flow processes each Excel workbook, matching expense records are added to this data table.

Finding the source Excel files

The flow then uses the folder selected by the user to locate the files that need to be processed. Because the source folder could potentially contain other types of files, the flow uses a file filter to find files ending in .xlsx. The resulting collection of Excel files is saved as a variable representing the source files.

Looping through the Excel files

With the source files identified, the flow can begin processing them. A For each loop iterates through the collection of Excel files one at a time. For each file, Power Automate launches Excel and opens the current workbook. The Excel instance doesn't need to be visible while this processing occurs. The source workbook can also be opened as read-only because the flow only needs to retrieve information from it. The flow then reads the full available range from the worksheet and recognizes the first row as the column headers. This gives the flow a table of Excel data that it can process.

Filtering Excel data

Once the contents of a workbook have been read, the flow uses the Filter data table action to identify the records that match the department specified by the user. For example, if the user entered Marketing, the filter looks at the Department column and retains the appropriate Marketing records. Another For each loop then goes through the filtered rows. Each matching row is inserted at the end of the combined data table created earlier. This process repeats for every Excel workbook in the selected folder. The result is a single data table containing matching records collected from multiple source files.

Closing Excel instances

After processing a source workbook, the flow closes the Excel instance. This is an important step when automating desktop applications. Leaving an Excel instance running after processing has finished can lead to unexpected behavior. During testing, an instance left running in the background caused problems when attempting to open and edit another Excel file. Closing each instance after it is no longer needed helps keep the automation clean and prevents those background processes from interfering with later work.

Testing the data processing

Before creating the final workbook, you can test the flow and inspect its variables. In the demonstration, the flow selects the source folder and uses Marketing as the department. It then processes the source workbooks and builds the combined data table as matching records are found. Inspecting the data table during testing makes it possible to confirm that the correct data has been collected before moving to the output stage.

Creating the final Excel workbook

Once the combined data table has been successfully built, the flow creates a new Excel workbook. This time, Power Automate launches Excel with a blank document. The entire combined data table can then be written to the worksheet at once. There is no need to loop through every individual row again. Because a new workbook opens with the active cell at A1, the flow can write the data beginning there. The workbook is then saved to the selected source folder with the department incorporated into the filename. After saving, the Excel instance is closed.

Testing and publishing the flow

After all of the actions are configured, run the flow again from beginning to end. The finished flow:

1. Prompts the user to select a folder.
2. Prompts the user to specify a department.
3. Finds the Excel workbooks in the folder.
4. Reads the source data.
5. Filters the records for the selected department.
6. Combines matching records into one data table.
7. Creates a new Excel workbook.
8. Saves the combined data into that workbook.

In the class demonstration, the flow successfully combined matching expense data from the January, February, and March source files into a single department-specific workbook. Once you've confirmed that the flow works as expected, you can publish it. From the Power Automate Desktop flow list, you can run the published flow again using the play button and provide the folder and department when prompted.

Start automating repetitive desktop tasks

Power Automate desktop flows can be a powerful way to save time on repetitive day-to-day tasks. The Excel example in this Fast Class is just one implementation. The same concepts of collecting user input, organizing actions, using variables, looping through information, applying conditions, and interacting with desktop applications can provide a foundation for building other desktop automations. Power Automate Desktop also includes Copilot to assist with creating flows. For more complex automation needs, Syvantis can help you explore and build Power Automate solutions that fit your business processes.