Concatenate Strings Using Power Automate Desktop

A client recently contacted us about an issue based on a Data table in Power Automate Desktop. They have two data tables on ‘Project details’. One table contains all project details, while the second table includes only the Project Name & Project Start Date.

Their requirement is to combine the values of the second data table with the first data table, which has all the project information.

To combine the data, Power Automate cloud has a concat() function. But it is not supported in Power Automate Desktop.

In this tutorial, I will show you how to concatenate strings using Power Automate Desktop. You’ll also see different examples, such as:

• Power Automate Desktop concatenate two text strings
• Combine a string and a variable in Power Automate Desktop
• Join multiple strings in Power Automate Desktop
• Power Automate Desktop merge strings from Data tables
• Join string values from Excel using Power Automate Desktop

Concatenate Two Text Strings Using Power Automate Desktop

Let me show you how to concatenate strings in a Power Automate Desktop using the ‘+’ operator.

Suppose I will take two strings, ‘Heniretta’ and ‘Mueller’. I wanted to concatenate these two strings with a space.

To do this, you can check out the steps below:

1. Launch Power Automate Desktop, then provide the flow name and click on the Create button.

2. Then, it will open the workspace window, where you can design the desktop flow by adding actions into the workspace.
3. From the Actions pane, search for ‘Set variable‘, drag and drop it into the workspace.
   • Variable: I have renamed the variable to ‘Name’.
   • Value: Provide the expression given below.

%'Heniretta' + ' ' + 'Mueller'%

In the above expression, place apostrophe marks before and after the text to make Power Automate understand it as a text; otherwise, it will be considered as a variable.

After that, click on the Save button to save the action.

4. Now, save the flow and click on the run button. Once the flow executes successfully, open the flow variable to check.
5. The screenshot placed below will show the output [Heniretta Mueller].

Concatenate a String and a Variable in Power Automate Desktop

In this section, I will explain how to concatenate a string and a variable using Power Automate Desktop.

Suppose I will take a variable value with ‘EmployeeID'[EMP001] and the Employee name [Lidia Holloway] as a text string. I wanted to combine Employee ID + Employee Name with a hyphen(—), for example, EMP001-Lidia Holloway.

Look after the steps mentioned:

1. On to the Power Automate desktop workspace, add a ‘Set variable‘ action to configure the Employee ID. Set the parameters and click on Save.
   • Variable: I renamed it to EmployeeID.
   • Value: Enter the value according to your preference.

2. Similarly, drag and drop one more ‘Set variable’ action to concatenate the string with a variable. Provide the parameters below and click on Save.
   • Variable: I renamed it to EmployeeIDName.
   • Value: Click on {X} -> Take EmployeeID variable and the text as provided in code below.

%EmployeeID + '-' + 'Lidia Holloway'%

Now, you can run the flow.

Output:

3. The image below will show you the output, which combines a variable and a string.

Concatenate Multiple Strings in Power Automate Desktop

You can also concatenate variables without using the + operator in Power Automate desktop.

To combine the variables, you need to take the variables separated by a space.

Suppose I have three string variables with Employee Names like [Heniretta Mueller, Joseph Brown, and Lidia Holloway]. Now, I wanted to concatenate these three variables separated by a comma so that the output will be like [Heniretta Mueller, Joseph Brown, Lidia Holloway].

See the steps below:

1. In the Power Automate desktop workspace, add three Set variable actions that will store employee names separately in variables.

2. Next, I will display the result. To do this, expand the Message box section and then drag and drop the ‘Display Message’ action to the workspace. Then provide the information below:
   • Message box title: Enter a suitable title for the message box.
   • Message to display: Provide the expression below.

%Name%, %EmployeeName%, %Name2%

The above expression has the variables separated by a comma and a space.

3. Now run the flow by clicking on the Run button. You can see the Message box will pop up and the result string.

This is another way of concatenating multiple string variables using Power Automate Desktop.

Combine Two Data Tables Using Power Automate Desktop

Now, I will show you how to merge the two data tables using a desktop flow in Power Automate.

Assume I have two data tables, ‘Project Details’, with one table containing’ Project ID, Project Name & Project Start Date & Status’, as shown below.

The other data table contains ‘Project Name & Project Start Date’, as shown in the screenshot below.

I now want to merge the values of ‘Project Name & Start Date’ from the second data table into the first data table.

To concatenate the values of two data tables, follow the steps mentioned:

1. Launch the Power Automate desktop app, create a flow.
2. To create a Data table, drag and drop the “Create new data table” action to the workspace. Add rows & column values to the data table. Click on the Save button.

Here, I have renamed the variables produced to ‘Projects’.

3. Similarly, I have added another data table that has details like [Project ID, Name, Start Date, Status]. I have renamed the variable of this data table to ‘Project Details’. Then, click on the Save button.

4. In the last step, take the ‘Merge data tables‘ action into the canvas. Set the parameters below, then save the action to the workspace.
   • First data table: Select the first data table variable.
   • Second data table: Click on {X} to take the second data table variable.
   • Merge mode: Choose the option according to your preference. I have selected ‘Ignore extra columns’.

Save the action. The values will be merged into the first data table variable.

Now, the flow is ready. Save and test the flow.

Output:

5. When the flow executes, you can see the result in the ‘Project Details’ variable. The screenshot shows the concatenated values from the ‘Projects’ data table.

Concatenate String Values From Excel using Power Automate Desktop

Here, I will explain to you how to retrieve and combine string values from an Excel worksheet using Power Automate Desktop.

Let’s consider that I have an Excel data set with the “Project Details” as shown in the figure below.

From the above Excel sheet, I wanted to concatenate and display ‘Project ID & Project Name’ in the format of ‘PJT1000’ & ‘California Highway Expansion’.

See the steps below:

1. First, to open the Excel instance on your local desktop, drag and drop the “Launch Excel” action to the workspace. Provide the information below. Save the action.
   • Launch Excel: Choose and open the following document option.
   • Document path: Click the insert file icon and select your Excel file.

2. Then, to retrieve the first free column or row of an active worksheet, I will add the “Get first free column/row from Excel worksheet” action. This will be useful to extract the end columns & rows of an Excel sheet dynamically.

3. To extract the data from an Excel sheet, drag and drop the “Read from Excel worksheet” action that is present under Excel. Enter the details below:
   • Excel instance: By default, it will take the Excel variable.
   • Retrieve: I have selected the “Values from a range of cells” option.
   • Start column: Enter the value as A.
   • Start row: Provide value as 1.
   • End column: Select FirstFreeColumn variable -1, because this will give the end column dynamically.
   • End row: Choose FirstFreeRow variable -1. This will get the end row dynamically without hardcoding it.

It will produce the ‘ExcelData’ variable, and click on the Save button.

Now, we have extracted the data from an Excel Worksheet.

4. Next, add a ‘Create new list’ action to create an empty list. This will produce a ‘List‘ variable.

5. Then, add the “Close Excel” action and click on the Save button to close the Excel instance.

6. Add a For each loop action. This will iterate over the list of items in an Excel sheet. It will produce CurrentItem as a variable.
   • Value to iterate: Select the ExcelData variable from the previous step.

7. Inside the For each loop, take a ‘Set variable‘ action to retrieve only the Project ID & Name. Then, click on the Save button.
   • Variable: I have renamed the variable produced to ‘ListofProjects’.
   • Value: Provide the expression.

'%CurrentItem['Project ID']%' & '%CurrentItem['Project Name']%'

From the above expression,

• Project ID & Project Name =Hardcoded Text
• ‘%CurrentItem[‘Project ID’]%’ = For each loop variable with column name.
• ‘%CurrentItem[‘Project Name’]%’ = For each loop variable with column name.

8. Then, from the Actions pane, drag and drop ‘Add item to list action. This action adds items to the list. Save the action.
   • Add item: Select ‘ListofProjects’ variable.
   • Into list: Choose the ‘List’ variable coming from the ‘Create New List’ action.

9. After the loop, add a Join text action that will combine the Excel column values with a New line. Configure the parameters as follows.
   • Specify the list to join: Select List variable.
   • Delimiter to separate list items: Choose the option ‘Standard’ from the drop-down.
   • Standard delimiter: Select the delimiter type. Here, I selected the New Line option.
   • Times: Enter how many times to use the specified delimiter.

The flow is ready now. Click on the Save button and run the flow.

Result:

Once the flow is executed, on the right side, you’ll find the Flow variables section. Check the JoinedText action.

This way, you can concatenate string values from an Excel worksheet using a Power Automate Desktop flow.

Unlike the Power Automate cloud, which uses the concat() function to combine the text strings, Power Automate Desktop does not have a direct concat() function.

In PAD, you can combine or join the strings simply by using a + operator or by using the Set variable with an expression or with a Join text action.

Other Power Automate Desktop Tutorials You May Read:

• Power Automate Desktop Text Actions
• Upload File in SharePoint Power Automate Desktop
• Retrieve Excel Column Values Into List Power Automate Desktop
•  Extract Data From a Web Page to Excel Power Automate Desktop
• Insert Column and Row into Excel Worksheet Power Automate Desktop
• Power Automate Send Reminder Emails

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.