Unanswered

Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

(0) Share

Report

Posted on by Navansh

Hi Community

I need help to create an array with data from other arrays. The unique key will be Code rest all may have different values. The Quantity parameter needs to be combined in each row. the Source Data is expected to be Array of 10000 "Array" and each "Array" can have about 100 to 200 objects. The Desired array is supposed to match the object on the basis of "Code" and add "Quantity" property from source array to Desired Output array as Q1, Q2, Q3, Q4, Q5, etc.

Source Data Array:

-------------------------

[

{

"Array": [

{

"Type": " ",

"Code": "",

"Item": "H",

"Quantity": "1"

{

"Type": "",

"Code": "",

"Item": "B",

"Quantity": "2"

{

"Type": "HW",

"Code": "123456",

"Item": "Test Item1",

"Quantity": "3"

{

"Type": "HW",

"Code": "789123",

"Item": "Test Item3",

"Quantity": "9"

}

]

{

"Array": [

{

"Type": " ",

"Code": "",

"Item": "H",

"Quantity": "4"

{

"Type": "",

"Code": "",

"Item": "B",

"Quantity": "5"

{

"Type": "HW",

"Code": "123456",

"Item": "Test Item1",

"Quantity": "3"

{

"Type": "HW",

"Code": "456789",

"Item": "Test Item2",

"Quantity": "6"

{

"Type": "HW",

"Code": "789123",

"Item": "Test Item3",

"Quantity": "9"

}

]

{

"Array": [

{

"Type": " ",

"Code": "",

"Item": "H",

"Quantity": "7"

{

"Type": "",

"Code": "",

"Item": "B",

"Quantity": "8"

{

"Type": "HW",

"Code": "456789",

"Item": "Test Item2",

"Quantity": "6"

{

"Type": "HW",

"Code": "789123",

"Item": "Test Item3",

"Quantity": "9"

}

]

}

]

-------------------------

Desired Output:

-------------------------

[

{

"Type": " ",

"Code": "",

"Item": "H",

"Q1": "1",

"Q2": "4",

"Q3": "7"

{

"Type": "",

"Code": "",

"Item": "B",

"Q1": "2",

"Q2": "5",

"Q3": "8"

{

"Type": "HW",

"Code": "123456",

"Item": "Test Item1",

"Q1": "3",

"Q2": "3",

"Q3": ""

{

"Type": "HW",

"Code": "456789",

"Item": "Test Item2",

"Q1": "",

"Q2": "6",

"Q3": "6"

{

"Type": "HW",

"Code": "789123",

"Item": "Test Item3",

"Q1": "9",

"Q2": "9",

"Q3": "9"

}

]

-------------------------

I have the same question (0)

All responses (7)

Answers (0)

v-jefferni on at

Like (0)

Report

Re: Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

Hi @Navansh ,

This is the outputs of my test flow:

and flow configurations are:

Outside of Apply to each you can use the variable where contains the array you need.

Best regards,

Was this reply helpful? Yes No
Navansh 27 on at

Like (0)

Report

Re: Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

Hi @v-jefferni

The actions listed above are applicable to have all the objects in the array but not adding the property to the object.
The required output is to have limited number of objects with their properties being added:

Desired Output:
-------------------------
[
{
  "Type": " ",
  "Code": "",
  "Item": "H",
  "Q1": "1",
  "Q2": "4",
  "Q3": "7"
},
{
  "Type": "",
  "Code": "",
  "Item": "B",
  "Q1": "2",
  "Q2": "5",
  "Q3": "8"
},
{
  "Type": "HW",
  "Code": "123456",
  "Item": "Test Item1",
  "Q1": "3",
  "Q2": "3",
  "Q3": ""
},
{
  "Type": "HW",
  "Code": "456789",
  "Item": "Test Item2",
  "Q1": "",
  "Q2": "6",
  "Q3": "6"
},
{
  "Type": "HW",
  "Code": "789123",
  "Item": "Test Item3",
  "Q1": "9",
  "Q2": "9",
  "Q3": "9"
}
]

Updating visualization from array to table:

All Arrays from Source Array:
Array1:
Type Code Item Quantity
H 1
B 2
HW 123456 Test Item1 3
HW 789123 Test Item3 9

Array2:
Type Code Item Quantity
H 4
B 5
HW 123456 Test Item1 3
HW 456789 Test Item2 6
HW 789123 Test Item3 9

Array3:
Type Code Item Quantity
H 7
B 8
HW 456789 TestItem2 6
HW 789123 TestItem3 9

Required Destination Array:
Type Code Item Q1 Q2 Q3
H 1 4 7
B 2 5 8
HW 123456 Test Item1 3 3
HW 456789 Test Item2 6 6
HW 789123 Test Item3 9 9 9

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

Re: Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

Hi @Navansh ,

Can't you get the result using the flow I created?

Best regards,

Was this reply helpful? Yes No
Navansh 27 on at

Like (0)

Report

Re: Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

Hi @v-jefferni

I tried the solution given earlier and it does not give the desired output. what we get is a union of all arrays with these steps:

JSON Output:
[
  {
    "Type": " ",
    "Code": "",
    "Item": "H",
    "Quantity": "1"
  },
  {
    "Type": "",
    "Code": "",
    "Item": "B",
    "Quantity": "2"
  },
  {
    "Type": "HW",
    "Code": "123456",
    "Item": "Test Item1",
    "Quantity": "3"
  },
  {
    "Type": "HW",
    "Code": "789123",
    "Item": "Test Item3",
    "Quantity": "9"
  },
  {
    "Type": " ",
    "Code": "",
    "Item": "H",
    "Quantity": "4"
  },
  {
    "Type": "",
    "Code": "",
    "Item": "B",
    "Quantity": "5"
  },
  {
    "Type": "HW",
    "Code": "123456",
    "Item": "Test Item1",
    "Quantity": "3"
  },
  {
    "Type": "HW",
    "Code": "456789",
    "Item": "Test Item2",
    "Quantity": "6"
  },
  {
    "Type": "HW",
    "Code": "789123",
    "Item": "Test Item3",
    "Quantity": "9"
  },
  {
    "Type": " ",
    "Code": "",
    "Item": "H",
    "Quantity": "7"
  },
  {
    "Type": "",
    "Code": "",
    "Item": "B",
    "Quantity": "8"
  },
  {
    "Type": "HW",
    "Code": "456789",
    "Item": "Test Item2",
    "Quantity": "6"
  },
  {
    "Type": "HW",
    "Code": "789123",
    "Item": "Test Item3",
    "Quantity": "9"
  }
]

Visualization of received output array from suggested solution:
Type Code Item Quantity
H 1
B 2
HW 123456 Test Item1 3
HW 789123 Test Ietm3 9
H 4
B 5
HW 123456 Test Item1 3
HW 456789 Test Item2 6
HW 789123 Test Item3 9
H 7
B 8
HW 456789 Test Item2 6
HW 789123 Test Item3 9

Required Destination Array:
Type Code Item Q1 Q2 Q3
H 1 4 7
B 2 5 8
HW 123456 Test Item1 3 3
HW 456789 Test Item2 6 6
HW 789123 Test Item3 9 9 9

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

Re: Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

Hi @Navansh ,

So, you need a Group by function on the data output from my test flow. Currently I have some issues on my VM so I'm not able to do more test. But I found a blog to perform similar actions on a dataset. Hope it could help.

Power Automate: Group By Multiple Columns on a Collection (dynamicsforcrm.com)

Best regards,

Was this reply helpful? Yes No
Navansh 27 on at

Like (0)

Report

Re: Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

Hi @v-jefferni

I have tried the solution given on link powerautomate-groupby-multiple-columns and seem to be stuck as the result is nowhere near the requirement.
I am sharing screenshots below:

output_file.zip

Arrays.zip

Was this reply helpful? Yes No
Matthy79 4,178 Super User 2024 Season 1 on at

Like (0)

Report

Re: Merge multiple arrays/table into a single table/array with common objects having their property added (multiple columns for common row)

Hello @Navansh

I would suggest you create a unique array of all the items using union. And after that you use xpath to search the xml converted input JSON to add the information you need.

Was this reply helpful? Yes No