How to merge data sets with the same key column in PowerAutomate

Question

I have two data sets coming from two separate API calls. These both give me a list of events, and a number value. I want to merge these two sets into one table, in PowerAutomate, but I am having trouble figuring out how -- I've tried a nested Apply to Each function but it's not successful.

Example of my data:

Array 1: | Event Name | Tickets | |------------|---------| | General Admission | 510 | | Members | 210 | | Special Programs | 100 | | Groups | 20 |

Array 2: | Event Name | Checked In| |------------|---------| | General Admission | 210 | | Members | 110 | | Special Programs | 100 |

(these data are in JSON format, arrays or strings: [{ "Event Name": "General Admission", "Purchased Tickets": "510"},{"Event Name": "Members", "Purchased Tickets": "210"}....] and [{"Event Name": "General Admission", "Checked In": "210"},.....])

And what I want to do is merge them into: | Event Name | Tickets | Checked In | |------------|---------|------------| | General Admission | 510 | 210 | | Members | 210 | 110 | | Special Programs | 100 | 100 | | Groups | 20 | |

(ideally the last cell would be 0 but that's something I can figure out myself). The important thing there is to note that both tables might not have identical rows, I just want to treat it as a null for now if so. I don't have a lot of control over what key values I get, those are dependant on the ran reports. However if they do match, the keys will be identical.

union just plops the second array after the first, and then I get a table with two sets of key values, and blank for column 2 and 3 respectively. I think inner-join can help me with this but it doesn't seem to give me an option to select both arrays.

I don't have access to any PowerAutomate premium features

Answer 1

A flow such as this will do it for you. I have to say, this type of manipulation like you're wanting to do is not always ideal on the LogicApps/PowerAutomate engine. Either way ...

To describe each step ...

Base Data

This is simply your JSON that is the base data you want to augment with the new data set.

Lookup Data

This is the other data set noting that the last record has no CheckedIn property. Obviously, that's an important part of the flow.

Initialized Combined Array

This is empty at this stage, it will combine the data in a joined state.

Process Each Base Data Item

This is obvious, we're just looping through the Base Data array.

Filter Lookup Array on Current Base Data Item

It's a bit easier to visualise this action.

To show you the code behind that step ...

"inputs": {
    "from": "@body('Lookup_Data')",
    "where": "@equals(item()['EventName'], items('Process_Each_Base_Data_Item')?['EventName'])"
},

Append to Combined Array

There are a couple of ways to do this but to illustrate it all a bit better, we're simply creating a new object and adding that to the Combined Array variable.

This is the code contained within the step ...

"inputs": {
    "name": "Combined Array",
    "value": {
        "EventName": "@{items('Process_Each_Base_Data_Item')?['EventName']}",
        "Tickets": "@items('Process_Each_Base_Data_Item')?['Tickets']",
        "CheckedIn": "@if(greater(length(body('Filter_Lookup_Array_on_Current_Base_Data_Item')), 0), body('Filter_Lookup_Array_on_Current_Base_Data_Item')[0]['CheckedIn'], 0)"
    }
}

The important expression being the CheckedIn property.

It checks to see if the filter result had at least one value, if it does, it gets the first (using index 0 but you could use the first function) item (I assume there should only be one) and then gets the CheckedIn property from that.

The end result is an array that looks like this ...

[
  {
    "EventName": "General Admission",
    "Tickets": 510,
    "CheckedIn": 210
  },
  {
    "EventName": "Members",
    "Tickets": 210,
    "CheckedIn": 110
  },
  {
    "EventName": "Special Programs",
    "Tickets": 100,
    "CheckedIn": 100
  },
  {
    "EventName": "Groups",
    "Tickets": 20,
    "CheckedIn": 0
  }
]