Join all non blank cell values into a string

[mohashinBhyian]

Dear Friend,

I am creating a flow. I am stuck on joining all non-blank cell values into a simple string. Like from the below table ("HTMLOutput"), I want to join the value of "Colorway No". My expected result is 01Blank21

Pls help.

[rzaneti · Accepted solution]

Hi @mohashinBhyian ,

For this example, I will be using this Excel table:

I will try to join all employees names.

Step 1 - Create a string variable:

First, let's create a variable with string data type to store your output. I'm calling it as 'final_text', but feel free to put any more meaningful name:

Step 2 - Loop table records:

Based on the shared image, you already retrieved the table records to Power Automate, which is great. Now we need to loop all of these records, by using an 'Apply to each' action and passing the 'value' dynamic content from 'List rows present into a table' action as input:

If you don't know how to work with dynamic contents, check this article that I recently wrote about it: http://digitalmill.net/2023/07/31/using-dynamic-content-in-power-automate/ 

Step 3 - Adding employee name into variable:

Inside the 'Apply to each' block, let's include an 'Append to string variable' action, setting the variable that we created in step 1  as 'Name' and the dynamic content of 'Employee' as 'Value':

Note: make sure to select 'Append to string variable', as there is also another action called 'Append to array variable'.

For your use case, you will just need to change 'Employee' for the dynamic content of the column that you want to append into the text. 

Output

Out of the loop block, I'm including a 'Compose' action, just to display the output of 'final_text' variable:

And this is the final result:

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 

[hrangel-gg]

The solution from rzaneti is missing a condition to skip blank values.

An alternative solution could be:

1. Use a Select to get the column data you want (use "item()?['MyColumnName']" as value)
2. Use a filter to remove the blanks from the select (use just "item()" as the result of the select should be an array of strings)
3. Use a join to concatenate the result of filter

[mohashinBhyian]

@hrangel-gg Thanks for sharing this. Can you please kindly share the Expression in detail?

[hrangel-gg · Accepted solution]

Sorry, the split used in the select was just because I was lazy and didn't want to recreate the Excel part so I used an in-place array. This time I created an Excel file like yours, see below.

• The select "From" uses the "Value" from the "List rows present in a table" action. For the map, click the button at the right of the field to switch to "freehand expression" and select your field (ColorwayNo from Excel action)
• The filter "From" uses the output from the Select. In the field (textbox in the left) just use "item()"; since the select list only have strings it refers to the current item. If you had objects you would need to use "item()?['MyField']"
• The compose just joins all values from filter action using an empty string ('') as second parameter.
  join(body('Filter_array_-_Remove_Blanks'),'')

Hope this helps to clarify the example.

[mohashinBhyian]

Simple, and easily understood, This is working fine with me.🖤🖤  @hrangel-gg
Great thanks for your help, This is also working 🖤@rzaneti