Combining multiple objects from single array without using a for loop

[HughBP]

Hello everyone, 

I am looking for a way to deduplicate items from my array objects and combine my array objects, so they look like the following:

{

    "Name": "David Henderson",

    "Weekday": "Tuesday",

    "Hours": "2.75000000000000"

    "Hours": "4.75000000000000"

    "Hours": "1.75000000000000"

  },

instead of being separate array objects. 

I really don't want to use a for each loop on it as we have over 100 employees and a lot of sites where people check in and out of, so the data sets are often above 6-7k entries by the end of the week and loops take forever. Simultaneously, I'm not sure if it's possible as the entries are extremely random and one person might have 1 or 10 check in's and time slots.

Any suggestions would be greatly appreciated. 

Below an example of dummy data. 

[

  {

    "Name": "David Henderson",

    "Weekday": "Tuesday",

    "Hours": "1.00000000000000"

  },

  {

    "Name": "David Henderson",

    "Weekday": "Tuesday",

    "Hours": "1.00000000000000"

  },

  {

    "Name": "Amy Moran",

    "Weekday": "Tuesday",

    "Hours": "2.00000000000000"

  },

  {

    "Name": "David Henderson",

    "Weekday": "Tuesday",

    "Hours": "2.00000000000000"

  },

  {

    "Name": "David Henderson",

    "Weekday": "Tuesday",

    "Hours": "1.00000000000000"

  },

  {

    "Name": "Amy Moran",

    "Weekday": "Tuesday",

    "Hours": "1.00000000000000"

  },

[tom_riha]

Hello @HughBP ,

you can't create such JSON object as each property name must be unique - it's not possible to have multiple "Hours" properties.

Other than that I'd use a similar approach as described in this article. Get only the unique Name values from all the objects, loop through all the Names and for each of them use 'Filter array' to get only the entries for that given Name. Then you can 'Select' them or count them depending on how you want to process them.

[HughBP]

Thanks for that. I'm trying to avoid a for loop in flow to speed it up as much as possible

I'm thinking I might try to combine the hours and days into an array and then going by length and adding the hours whilst deduplicating the days? That might potentially make it faster.   

[tom_riha]

Hello @HughBP ,

I don't think you can do that without any loops, but I'd love to see the solution if you do.

[eliotcole]

Hi, @HughBP , union() is your friend, here.

So here you can see that I have an array with multiple duplicates, and what I do is utilise the union() function (used to fuse arrays) which removes any duplicates as part of its process. However, instead of using separate arrays, I just use the same one twice.

union(outputs('arrayCNST'), outputs('arrayCNST'))

[eliotcole]

I'm sure you knew this, mate, and it just didn't ping ... but union() is the great equaliser of same data. 😉

[HughBP]

Hello! Thank you, but I’m actually trying to add those numerical values and not deduplicate 🙂 

[takolota]

@tom_riha @HughBP @eliotcole 

You could use a Filter array action & a template for the Run script action described here (https://www.tachytelic.net/2021/03/power-automate-sum-array/?amp) to at least reduce the number of Apply to each runs to just the number of employees that you have.

Then depending on your needs & the amount of time you have, you could probably find and/or develop a script for the Run script action that aggregated hours by employee without needing separate Filter array, Run script, & Append to array actions inside an Apply to each loop.

[eliotcole]

Ah, yes, I'd missed the first part.

I do have something for this, though, and union() will likely come in ... but yeah, good point.

Need to sleep though!

[eliotcole · Accepted solution]

OK, @HughBP , I've had forty winks ... what you see below is two initial, quick steps before a single, frugal, Apply to each.

One 'Apply to each' With A Small Footprint

The one big assumption that I've made from your example data is that you're running this once per day, for that day's hours. I've added an addendum to cope with the eventuality that you're not.

This can work anywhere in your existing flow, I've used arrayCNST to represent the entirety of your current flow which provides the example data you gave. So any references to that you'd want to obviously replace with your own array.

Part 1 - The Setup

Firstly, you setup an array of names, and an empty array.

Initialize realArrayVAR

The empty array is easily handled with a variable.

Select Names

The array of names is just simple Select action leaving you with an array of names, in your case, multiple David and Amy values!

So those need to be distilled down to just the names, with no duplicates, that will be handled in the first part of ...

Part 2 - The Apply to each

Here, you run an Apply to each on a union() of the array of names to filter down to just the names required.

This drastically reduces any loops by reducing any duplicate names:

union(body('Select_Names'), body('Select_Names'))

So from this example you'd be left with:

[
  {
    "Name": "David Henderson"
  },
  {
    "Name": "Amy Moran"
  }
]

Filter

So, now that you're inside that loop, the first step is to use a Filter action on the original array, to filter to only the current name. This would produce, for example:

[
  {
    "Name": "David Henderson",
    "Weekday": "Tuesday",
    "Hours": "1.00000000000000"
  },
  {
    "Name": "David Henderson",
    "Weekday": "Tuesday",
    "Hours": "1.00000000000000"
  },
  {
    "Name": "David Henderson",
    "Weekday": "Tuesday",
    "Hours": "2.00000000000000"
  },
  {
    "Name": "David Henderson",
    "Weekday": "Tuesday",
    "Hours": "1.00000000000000"
  }
]

jsonObject

Now that needs to be summed up in an object to be placed into your new, simpler, array, so let's construct that object in a Compose action, I called it jsonObject, with this basic format:

{
  "Name": "string",
  "Hours": 0000
}

If you want to skip this action you can, as per the note you see on my Append to array action.

The name just takes the name from this Apply to each run with a simple "items('Apply_to_each')?['Name']" expression.

In order to work out the hours, though, you need to do some more complicated work. So, thanks to @takolota's prompting, and this blog, there's a nice xpath() calculation to get the hours. This is the long expression is used to calculate that (int() is now float() to allow partial hours):

float(
  xpath(
    xml(
      json(
        concat(
          '{"r":{"v":', 
          body('Filter_array'), 
          '}}'
        )
      )
    ), 
    'sum(//v/Hours/text()[number(.) = number(.)])'
  )
)

The xpath() function takes the Filter action data, ensures the hours are numbers, and adds them all up! The json(concat()) is just wrapping the filter into a structure that the xml() function can understand.

That produces the following final output:

[
  {
    "Name": "David Henderson",
    "Hours": 5
  },
  {
    "Name": "Amy Moran",
    "Hours": 3
  }
]

As you might be able to guess, I can probably make one with no Apply to each stage after my interview. But this should suffice.

I also left the original code for the Append to array action, which does away with the need for the jsonObject Compose action.

Addendum

Oh, and if you wish to do multiple days, you can just add in the additional logic either around, or inside the Apply to each.

Spoiler

Adding it around, for example, wouldn't add that much additional working for the flow. You would just need to do a parallel select on the days, and union() them together in a new Apply to each, and drag the existing one right in there.

Your final result will look something like this:

[
  {
    "Name": "David Henderson",
    "Weekday": "Tuesday",
    "Hours": 16
  },
  {
    "Name": "Amy Moran",
    "Weekday": "Tuesday",
    "Hours": 9.5
  },
  {
    "Name": "David Henderson",
    "Weekday": "Wednesday",
    "Hours": 16
  },
  {
    "Name": "Amy Moran",
    "Weekday": "Wednesday",
    "Hours": 9.5
  }
]

But you could completely have that be an array of two days, each with their own array of entries, if you liked. It would just require an extra array variable, and a bit of cleansing on each 'daily' loop.

[HughBP]

That is awesome! Thank you so much!