Is there a way to concatenate arrays (not Union() -- I don't want to drop any rows; not For Each/Append To Array -- it's a performance killer)

[MelissaReed]

I am pulling billing data off of a form that displays as two columns.  The reader pulls these two columns as a table of detail lines -- GenericCharges1A and GenericCharges1B.      

I need these values (there are 6 of these tables/arrays) consolidated to a single array for processing.

union() doesn't work because it drops duplicate rows.

Is there a unionall() that doesn't drop any rows?

I've tried concatenate() but it does literally just that -- it bolts the contents of the arrays together to form a long string and I can't use it aw an array.  I've tried wrapping that in createArray(), but it creates a two-item array with each item itself being an array.  

Has anyone found a way to do this?   Would pasting these values to an Excel table (virtual, not one that is saved anywhere) be an option?  

I could always do a For Each / AppendToArray() loop on each of the GenericCharges* arrays but that is a performance killer.

I'm looking for options that are NOT Union() or For Each.

[ekarim2020 · Accepted solution]

Hi @MelissaReed ,

Here is one option to combine multiple arrays into a single array without using an Apply to Each loop. The demo flow uses three arrays.

The flow works by converting each array into a string so that we can use Power Automate's string manipulation functions. We remove the enclosing characters of the array (i.e. [ and ] characters are removed ) using the slice function. The sliced strings of each array are then concatenated together, and the concatenated string is then converted to back in to an array.

(1) The demo flow uses three arrays. The red areas show what the slice funcion will extract from each array later in the flow. Note that the enclosing characters of the array (i.e. [ and ] characters) are removed.

 

(2) We can remove the enclosing characters of the array (i.e. [ and ] characters) using the slice function.

These are the expressions used in the compose actions above:

slice(string(variables('varArray1')), 1, -1)

slice(string(variables('varArray2')), 1, -1)

slice(string(variables('varArray3')), 1, -1)

See: Reference guide for expression functions - Azure Logic Apps | Microsoft Docs | Slice Function to see how the slice function works.

(3) The sliced strings from each of the three arrays are then concatenated together:

 

This is the expression used for the concat function:

concat('[', outputs('Compose_Array1'), ',', outputs('Compose_Array2'), ',' , outputs('Compose_Array3'), ']')

In the concat action we add back the enclosing characters of the array (i.e. [ and ] characters) which we removed earlier. The output of the concat function is a string, even though it looks like an array.

(4) Finally we need to convert the string from the concat function into an array using the json function . The json function will return an array of objects for the concatenated string:

 

This is the expression used for the initialize array variable function:

json(outputs('Compose_Concat'))

And here is the sample runtime output showing the combined array:

 

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[eliotcole]

Here's a one shot that uses the slice() function that @ekarim2020 showed you, @MelissaReed.

If you put this into a compose (or array Variable, or anywhere!) it is similar to Ellis' version, but just passed through a couple of translation layers instead:

json(
    xml(
        json(
            concat(
                '{"root": { "items": ', 
                slice(string(outputs('arrayOneCNST')), 0, -1), 
                ',',
                slice(string(outputs('arrayTwoCNST')), 1, -1), 
                ',',
                slice(string(outputs('arrayThreeCNST')), 1, -1), 
                ',',
                slice(string(outputs('arrayFourCNST')), 1, -1), 
                ',',
                slice(string(outputs('arrayFiveCNST')), 1, -1), 
                ',',
                slice(string(outputs('arraySixCNST')), 1), 
                '}}'
            )
        )
    )
)?['root']?['items']

I've just got the example arrays in 6 compose actions, rather than arrays, but it works the same way.

Working

Spoiler

 Here's each step from the inside out:

1. slice() - You have seen how these work in Ellis' example, here the first and last are just keeping that array delimiter.
2. concat() - This is placing each of those slice() functions inside some extra text which builds the JSON object, and ensuring that there are commas after the end of each of the last items in the inputted arrays.
3. json() - This converts whatever string is inside it into JSON proper.
4. xml() - This converts anything inside it into XML. XML works as arrays.
5. json() - This converts that back into JSON, and it is taking the value from the 'items' field which is itself inside the root item.

---

That being said ... I've put a bit of thought into the whether or not:

1. There's always going to be 6 arrays.
2. How that information is coming in.

So if you could show screenshots of your current flow in the original question, that would really help. Just obfuscate anything private like you have in the image of the information that is used to create the arrays.

I believe that there's at least two possible ways to do it in a couple of steps without knowing what the arrays are, but each one might be different depending on how it comes in. Ideally it would also be great to see how that information is presented in a (failed?) flow run, too.

My current thoughts which you can ignore, but could help if you want to go a bit deeper here:

Spoiler

I'm guessing that the arrays are coming in one of two ways:

1. You're sending out actions to retrieve the information, which literally creates 6 separate arrays.
2. You're receiving data from somewhere that has translated that form, which presents it as 6 arrays within the JSON data that it presents.

So if this is #1, then you could write your own 'megaArray', with each item being details on the 6 arrays AND the items from each array as an internal array field in that item. Then after that, use a Select action on a range() of the length of your 'megaArray', to selectively take an item from each of the 6 arrays, and place it as the current item in the new, select, array. Enabling you to place the correct details from each item in the original array inside this new one.

Alternatively, if it comes all in one package, as [[ARRAY_ONE],[ARRAY_TWO],[ARRAY_THREE],[ARRAY_FOUR],[ARRAY_FIVE],[ARRAY_SIX]], then you could do the same, but instead of making your own array, you use a select action to make it, which is even easier. This would be awesome if true, as your logic would be even easier.

I am putting together demos of both ways, because I want it for my own notes, but either option is more complex  than the above, or Ellis' example ... it's just that they're potentially data agnostic ... which is the ideal, here. Plus, two steps. That's why it'd be great to see how the information is coming in, or some kind of example.

[ekarim2020]

Sorry, I forgot to mention the union function if your source data is in the correct array format:

union(variables('varArray1'),variables('varArray2'), variables('varArray3'))

When you recieve data from sources that returns only text (string)  or partial data, then string manipulation functions are very useful to structure your JSON and array objects.

Ellis

[MelissaReed]

@ekarim2020 

This provided a missing puzzle piece -- slice().   Plus I do not have a good grip on when an array is really a string and vice versa...  This is enormously helpful just as a study in itself.

I will definitely give this a try.   The data I'm working with is coming in from a Forms AI output, where the form model is reading in charge detail that comes in two columns per page.  A short bill could just be a single column of data, a longer bill could be two columns on one page.   The Multi-page table (Preview) option won't work for me in this case because I have to read down, accross, then next page down, across.  Six tables:   1A, 1B then 2A, 2B, then 3A, 3B.  

So far none of our invoices details have needed tables 3A or 3B but I'm including them to allow for future growth.   

Your solution helps me skip a step -- the data comes from the "Read Forms Input"  as a part of the Ouptut() string  

The individual tables are referenced as GenericDetail1A entries, GenericDetail1B entries, and so forth.  But not every invoice will have all 6 tables.  I've accounted for that using Coalesce()

So with your approach I can take that text directly into your Slice() functions, no convert-to-string needed!

I tried the concat() approach but the data was already in array format from  Select actions. 

The secret was concatenate STRINGS not arrays, and a String in proper format can be converted en masse to an array variable -- no looping needed.  

This bit of knowledge is going to be applicable in so many places!

I want to make sure this will work in my particular case before clicking Accept as Solution but this sure looks like a winner!

[MelissaReed]

Union() is not an option for me because it drops non-unique rows.   I will have a lot of detail lines that appear to be identical and would be dropped if I used a Union() call.

[MelissaReed]

Thanks so much for your response/ideas.   I always like doing things in one call vs. multiples once the process is proven.  I'll probably stick with separate calls initially while I'm building.  Easier to debug.

And I love your cool dropdown/hidden blurb -- how do you do that?! 

Your questions:

Where is the data coming from?    This is coming in as Output() from a call to AI Forms / Read Form Input action.  If you haven't worked with this before, it is a crazy-complex structure but it is a string.    

The form that is being read is an Invoice with a varying number of detail lines presented in two columns per page, going from 1 to 3 pages.   In the Modeler, I have mapped each page/column's billing detail content to a table variable in the modeler:  GenericDetail1A, GenericDetail1B, then GenericDetail2A, GenericDetail2B, GenericDetail3A, GenericDetail3B  

When the AI Reader is executed it returns an output that contains these tables but only if they exist in the form being read.  If it is a "short" invoice, Output() won't have a reference "inapplicable" variables -- GenericDetail2A, 2B, 3A, 3B won't be part of the data model returned in Output()

I access the column/page block by referencing the dynamic variable "GenericDetail1A entries", "GenericDetail1B entries", etc... 

The snag is when a GenericDetail* value is not returned -- if referenced (ex: Select action), the flow will fail.

Workaround:  Use coalesce() to default to an empty array if the "real" table isn't there.  If there's a better way to deal with this situation I'd love to hear it!  

Will there there always be 6 arrays?   

The short answer is no.  The model defines 6, but the result coming back from the Form Reader on any particular invoice will return only the ones that were "used".   On a short bill with only a few charge lines, it will return GenericCharges1A but none of the rest in its "output()".   But they are used in succession if there's a 3B there's a 3A, if there's a 3A there's a 2B and so forth 

But what I'm trying to do is take those component clumps of data and reassemble them in the correct order to create a single list of the charges that I can then process -- identify header/footers, pluck off data elements like phone # from section head rows to apply to the detail lines. 

What's in the arrays?

Embarrassingly simple.

[

  {

    "Charge Name": "nFO - Business Fiber Voice Service",

    "Charge Amount": ".00"

  },

  {

    "Charge Name": "Voice Service Charges for ### 363-####",

    "Charge Amount": ""

  },

  {

    "Charge Name": "Access Recovery Chrg-Multi Line Business",

    "Charge Amount": "3.00"

  },

  {

    "Charge Name": "CALL FWD BUS. **",

    "Charge Amount": ".00"

  },

  {

    "Charge Name": "CALL ID(BUS) **",

    "Charge Amount": ".00"

  },

  {

    "Charge Name": "INTERSTATE ACC CHG - BUS-MULTI",

    "Charge Amount": "9.20"

  },

  {

    "Charge Name": "FUSC MULTILINE **",   

etc......

This raw array/table doesn't just contain the detail-level data, but also section header/footer data that will be processed out after all the component tables are reassembled and processed (for that I definitely need a For Each loop).

I think you've put me on the right track tho with "start with a string of array data, strip out [ ], then concatenate those results together as an array.   

[eliotcole]

OK, knowing that this is in each of the arrays really helps:

[
  {
    "Charge Name": "nFO - Business Fiber Voice Service",
    "Charge Amount": ".00"
  },
  {
    "Charge Name": "Voice Service Charges for ### 363-####",
    "Charge Amount": ""
  },
  {
    "Charge Name": "Access Recovery Chrg-Multi Line Business",
    "Charge Amount": "3.00"
  },
  {
    "Charge Name": "CALL FWD BUS. **",
    "Charge Amount": ".00"
  },
  {
    "Charge Name": "CALL ID(BUS) **",
    "Charge Amount": ".00"
  },
  {
    "Charge Name": "INTERSTATE ACC CHG - BUS-MULTI",
    "Charge Amount": "9.20"
  }
]

If I look at what is in the pre-processed image, and what you have there, I'm seeing that you've done some work on that data already.

Would that be a bad read of the situation?

I still think it can be done without an apply to each, it'll just take a bit of doing is all.

Irrespective of any logic in there, I would assume that you wish to split any 'mega' array on:

Voice Service Charges for ### 363-####

As that appears to define the separate batches, there.

If you don't wish to expose your flow here, you can send me a private message with it in, as I think that it doesn't appear to be including the multiples (1, 2, etc) that are before the charge descriptions ("CALL FWD BUS. **", etc), and surely you'd want that data, right?

I can't afford to pay for AI just to see how it handles / outputs that image, though, apologies. 😅

I've obviously a few ideas about how you can handle the multiple inputs, but it depends on where they're coming from, that's all. For example, if each array is coming from the same thing, then the slice() stuff might be pointless in its current context, or it might only need to be written once within a clever Select action that works on a range() function.

[MelissaReed]

I totally agree -- AI is too expensive to purchase it to troubleshoot for someone else!

But the heart of my question is one of array manipulation, not AI Forms.

Yes, what I sent is the result of taking that crazy-complex Output() with GUID-named columns and creating the simple two-column table I modeled in the first place using a SELECT on the Output() from AI Reader.  

The heart of my question is not an AIForms one, but array manipulation -- how do I take two or more sets of array/tabular data stored in separate variables and append them (not join, append) together.  

The ideal option would be Union() but it drops duplicate rows (I will have duplicate charges) and there is no UnionAll()

For me the solution as suggested by @ekarim2020  is: 

1)converting each table/array to a string

2)stripping off the leading [ and trailing ] on each of those strings

3)reassembling those strings using concat()

4)convert that long string back to an array using json()

This can be done in one nested function as I believe you had suggested.  

So I've extracted just the values I need for each Page/Column table using Select  -- no getting around that because the column reference IDs are uniquely named.

Then in a nested function call in Select/From create the consolidated result set I need as a starting point for processing the charges.    

[eliotcole]

So glad that you have a solution, Melissa!

I will say that you still have the problem of when more arrays need processing, but I guess you can save that for another time. 🙂

That's why I really wanted to see an actual representation of what you have that's showing you the arrays.

With that, I could make the solution provided so that it doesn't have separate slice functions, and instead relies on one or two select actions. Ideally you want to be data agnostic.

[ekarim2020]

Thanks for sharing your feed back!

Ellis

[MelissaReed · Accepted solution]

I just came across an excellent and "in one step" way to concatenate arrays   (append without dropping duplicates): 

 https://recursion.no/blogs/power-automate-extract-combine-join-and-filter-array-values/    

"convert the array to string object, and then concatenate them and replace the brackets in between. Then you need to convert it back to an array object afterwards."

json(replace(concat(

                                   string(outputs('arrArray1'))

                                   ,string(outputs('arrArray2'))

                                  ,  << and so forth for each array >>        

                      ),   '"]["',    '","')  )

Note carefully the replace() values are 

             singlequote, doublequote, end bracket, startbracket, doublequote, singlequote  

and 

            singlequote, doublequote, comma, doublequote, singlequote.

It's kinda hard to see when they're rammed up together 

Also I put commas at the start of the line for each array I'm appending.  The array expressions do need to be separated by  commas! 

This is much easier (and reproducible ) than the other solutions I've seen so I am going to mark this as Solution so more people can find it.