How to make an array of comma separated values?

[SPOD]

I have a dynamic data from excel that may contain ID’s separated like this "collaborators": "\"473\",\"617\"", The value can be blank or it may have a single value 473 It may may ‘n’ number of values always separated by commas. The formatting is coming through with escape characters, in Excel the values look like "473","617".  Initially they were formatted 473,617 and the values came through combined 473617 

The required output is a json array if blank then “”, or has one value [1] and a comma separated array if more than one value [473,617] So far I have tried:  array() 2) join(array(),’,’) and it gives “” how to get an array of comma separated values?

[v-litu-msft]

Hi @SPOD,

You could initialize an array variable and set it to empty, then use Apply to each to loop the rows in Excel table, for example:

 Run result:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[efialttes]

Hi!

Did you also try with json() ?

Hope this helps

[SPOD]

I didn't know about json() and i looks like it could help but its not working.  The inbound value from excel is this "collaborators_Formatted": "\"473\",\"617\"" 

I've also tried wrapping json() in array() and its created this output that is closer but it still only includes the fist value and (missing the 617) and its also returning a string and it must be an integer 
"collaborators": [
      "473"
    ]
My expression 
json(triggerBody()?['entity']?['collaborators_Formatted']) is returning "collaborators": "473" so its not in an array and only included the first value, it missed the 617

"collaborators": "473"

[abm · Accepted solution]

Hi @SPOD 

Thanks for the screen share. So the format of the data from excel was "1,2,3,4". In your HTTP Request we need the data to convert into array, so we did added a compose with two square brackets ("[ ]") then map the value from the excel sheet. Finally mapped the value to body of the HTTP. Next added a length() and trim() expression to check whether the value is blank then do not pass that value to HTTP body.

Hope the session was helpful.

@efialttes & @v-litu-msft  I got direct request from @SPOD  to help him. Didn't know it was posted already. Thanks for your post and being helpful for the community.

Thanks

[SPOD]

@abm thanks so much for the generous help with this, your solution is exactly what i was looking for.

[efialttes]

No problema @abm !

Glad to see issue reported by @SPOD was finally solved thanx to your advice

Thanx for making this community great!

[abm · Accepted solution]

Hi @SPOD 

To convert a series of numeric values (eg: 942,944,946,947) into a array of string values please do the following:

Above the compose split expression is as follows:

split(outputs('Compose'),',')

Then next inside the Apply to each Append to array use the following expression

string(item())

Final compose 3 you can ignore it. I have added this for my test. In your case you could pass the Array variable (ArrayValues) under the body of your API request.

Here is my test result

[SPOD]

@abm Once again, many thanks for the help with this and your solution is working as per your explanation.  The apply to each step was not required in my flow, here is the working flow:

The output is an array of string values in quotes separated with commas, perfect. thank you.

"direct_focus_area_ids": [

      "48",

      "49",

      "50",

      "51"

    ]

[SPOD]

Hi @abm 

Some further assistance on this on this same topic please, I have a condition checking the length of the array and branching if the array is blank.  I'm using length() > 0 but unfortunately a blank array is coming through as [“”] and the length calculates as 1.  I changed the condition to greater than 1 and this worked except a value with 1 entry [“1”] also calculates as length = 1.

What is the best way to add a condition to test if array = [“”] or isblank() in excel speak?

[SPOD]

Just for completeness, this is the current filter that is not working because [""]=1

[SPOD]

Hi, thanks everyone for supporting my journey from excel into power automate.  I've actually learned quite a lot about this area and will document my observations in case others walk this path.

The requirement was to take data from an excel row update and convert it into a json array with values as strings in quotes, comma separated like [] or [“1”] or [“1”,”2”,”3”]

This is done through adding a compose action using a split expression and the delimiter is  ‘,’

split(triggerBody()?['entity']?['direct_focus_area_ids_Formatted'],',')

The output of split is an array formatted in the required shape:

[

  "48",

  "49",

  "50",

  "51"

]

However an empty string comes  through as [“”] and this caused an error on the api I was using to POST.

The best practice advice for determining if a string has values is to use a condition to test if length() = 0 however this did not work in this case because [“”] and [“1”] both evaluate to one.

My solution was to use a test to check if the compose output was (not) <> = [“”], this was accomplished using an expression in the condition compose output <> array(‘’) which evaluates a <> [“”]

Because [“”] creates an error on my api, the branching logic I've used points to a version of the HTTP put that omits the column if the compose output = [“”]

[abm]

Hi @SPOD 

Could you post a run time history of your flow results where it shows [""]

Thanks

[SPOD]

Hi @abm , here is the post from history. I think the "" is because excel lacks a true null and this value comes from a formula that resolve to "". In any case the value causes an error so my solution uses conditions to evaluate if the compose step is equal to ["'] and if true, then pass it to a version of https that skips that column.  This is a heavy solution, not the least i must maintain 4 separate versions of http action with different json for each. Can you think of a better way? Would replace work from the first step? 

Another complexity that i haven't dealt with yet, there is another column that also may be blank, have one value of have many values "1","2","3" and if not ''" then i need top pass a next json object. I has thinking of adding a condition but having all the https action versions makes this even more hairy.

[abm]

Hi @SPOD 

Declare a boolean variable out side the apply to each loop when you construct the array values. Set the flag to true inside the loop. Later you can check this boolean flag to make sure whether the array is null or not?

[SPOD]

Hello v-litu-msft, I've been looking at your solution and think its on the right track.  Ive raised a new thread here, can you take a look and let me know your thoughts on it? How-to-create-an-array-of-integers-from-SPLIT