Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 38 ยท Desktop Flows

Variables, Excel and Files in Desktop Flows

Work with desktop flow data: variable types, the percent syntax, lists, data tables and rows, reading and writing Excel and text files, and looping over files and rows.

A desktop flow that only clicks is not much use; it must also carry data from place to place: rows from a spreadsheet into a form, text from a window into a file, numbers into a calculation. This lesson covers how data is stored and moved in Power Automate for desktop: variable types, the % syntax, the List and Data table types, and the Excel, file and text actions you will use most.

The application runs on Windows only, so the actions are described in words. The runnable examples are JavaScript stand-ins that model the data handling.

Variables and the percent syntax

You create a variable with Set variable (name and value) or implicitly as an action's output (every action that produces a value has a Variables produced section where you name it). To use a variable anywhere in a text field, wrap its name in percent signs:

TEXT
Invoice %InvoiceNumber% for %Customer% is due on %DueDate%

Inside an expression field (like an If condition), you can still use %-names; the designer inserts them with the button.

Common data types:

Type Example Notes
Text Hello Operations: Split, Replace, Trim, Parse to number
Numeric 42, 3.5 Math in % expressions: %Price * Quantity%
Boolean True / False Used in If and While
Datetime a date and time Add, subtract, format with Date time actions
List [a, b, c] Zero-based index: %MyList[0]%
Data table rows and columns %Table[0][1]% (row 0, column 1), or %Table[0]['Name']%
Data row one row of a table %CurrentRow['Amount']%
Custom object key and value pairs %Obj.Name% or %Obj['Name']%
File, Folder objects with properties %File.Name%, %File.Size%, %Folder.FullName%

Be careful with types: a value read from a text file is text, so %A + B% may join text rather than add numbers. Convert with Convert text to number (or Convert number to text) and test.

JS Stand-in: percent substitution and indexes

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Lists and data tables

A List is one column of values; a Data table has rows and columns, like a sheet. Actions in the Variables group let you add items, remove them, sort, shuffle, and insert or delete rows and columns. A For each loop gives you the current item (a value for a list, a Data row for a table):

TEXT
For each CurrentRow in %ExcelData%
    Display message  Name: %CurrentRow['Name']%  Amount: %CurrentRow['Amount']%
End

Column names exist only when the table was created with a header row (for example, Read from Excel worksheet with First line of range contains column names ticked); otherwise use numeric column indexes.

Excel automation

The Excel group works through the installed Excel application, so Excel must be on the machine. A typical sequence:

  1. Launch Excel (open an existing file; the output is an Excel instance variable, %ExcelInstance%).
  2. Read from Excel worksheet (all available values, or a specific cell or range) into %ExcelData%.
  3. Loop over the data and process it.
  4. Write to Excel worksheet (a value, a variable, or a data table, to a cell or the first free row).
  5. Close Excel (and save first when needed).

Key points: when the file is opened by the flow it can be opened invisibly, which is faster; always close Excel in every case, including after errors, or a hidden Excel process stays in memory and locks the file. Use Get first free column/row from Excel worksheet before appending data so you do not overwrite existing rows.

For servers with no Excel installed, use the Excel Online connector in a cloud flow instead, or work with CSV files using the file and text actions below.

Files, folders and text

The File and Folder groups cover everyday tasks:

  • Get files in folder (with a filter such as *.pdf) returns a list of File objects.
  • Copy, Move, Delete, Rename files; Create folder.
  • Read text from file and Write text to file (choose the encoding, and whether to append or overwrite).
  • Read from CSV file and Write to CSV file use data tables.
  • If file exists and Wait for file help with timing.

The Text group has Split text, Replace text, Trim text, Parse text (regular expressions), Convert text to number and Get subtext.

JS Stand-in: parse CSV text and total a column

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Inputs, outputs and secrets

A desktop flow can have input variables (values supplied when it starts, such as an invoice number from a cloud flow) and output variables (values it returns). Create them in the Input/output variables pane. Each has a name, a type and a default value. Mark an input as sensitive (Text type) for passwords; sensitive text is hidden in logs and cannot be shown with Display message. Retrieve credentials from a secure store rather than hard-coding them in an action.

Good habits

  • Name variables by meaning and keep one purpose per variable.
  • Validate data before you type it into another system: a bad row costs less to skip than to undo.
  • Clear large variables when done (a huge data table stays in memory).
  • Log progress to a file for long loops, so a failure at row 812 can be diagnosed.
  • Keep the amount of data modest; desktop flows are not a data pipeline.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How is a variable named Customer written inside the text of a desktop flow action?

    Desktop flows use percent signs around the variable name.
  2. 2. How do you read the Amount column of the current data row inside For each?

    Data rows are indexed by column name, which requires a header row to be read.
  3. 3. Why close Excel in every case, including after errors?

    Always release the application so later runs can open the file.
  4. 4. A value read from a text file is 10. What happens with %Value + 5% if you did not convert it?

    File text is text. Convert to a number before arithmetic.

Summary

  • Variables are used with %Name%; know the types (text, numeric, boolean, datetime, list, data table, data row, custom object, file, folder).
  • Lists and data tables are indexed from zero; column names need a header row.
  • Excel automation uses the installed Excel: launch, read, process, write, and always close.
  • File, folder and text actions cover CSV and text processing; convert types explicitly and validate before using the data.
  • Input and output variables connect desktop flows to cloud flows; mark secrets as sensitive.

Next: Attended, Unattended and Cloud-Triggered Desktop Flows