Export varying values from multiple array objects into single table row

[blairing26]

I have list of 100 stores.  The store information is to be printed on flyers in their area for advertising that location and the additional locations in the close vicinity. Some stores print just their single location info. but others are grouped together in a team of 2, 3, or 4 locations so they can share the advertising materials with all of their locations listed.
Example:

Single location Listing:
Cleveland (city of store)
123 Street Ave., Suite 3
(123) 193-2034
URL

Team of stores Location Listing:
Taylorsville  -  1620 T Street
Fayette  -  63 N Ave., Suite 3
Smithtown  -  6th Ave, Unit 6
(612) 442-3857
 URL

Each store has multiple columns of information that are unique to their store. (Store ID, Address1, Suite/Bldg, City)
Each Store that is grouped with another, has a Team ID that matches the other stores in their group and they share some information between them.  Ie. The URL and sometimes the phone numbers.

For data merge purposes, I need all of the values from the columns of each store that is in a Team, to be listed out in a single Excel Row.  Empty column values are ok. 

I have tried a zillion diff. combinations of flows expressions, apply to each combinations and can’t seem to get the array objects into one row in excel.  I’m thinking that if I start with a union expression to get the unique Team ID’s and a length expression to count how many rows are found with that same Team ID, then use the Team ID to filter the array and maybe the row count could be used to determine which column to start populating with the values from the first store array and which column to start populating with the values of the second store array, etc. for the 3rd or 4th stores to populate additional columns in that single row. 

See the below table of data and then an example of what I’m trying to get.   

Store ID	Team ID	Address	Suite/Bldg	City	Phone	URL
13548	1	Store1Address	Ste A	Store1City	Store1Phone	Team1URL
64815	1	Store2Address		Store2City	Store2Phone	Team1URL
45822	2	Store3Address	Bldg 2	Store3City	Team2Phone	Team2URL
44782	2	Store4Address		Store4City	Team2Phone	Team2URL
69732	2	Store5Address		Store5City	Team2Phone	Team2URL
87385	3	Store6Address	Unit 5	Store6City	Store6Phone	Team3URL
48676	3	Store7Address		Store7City	Store7phone	Team3URL
99949	3	Store8Address		Store8City	Store8phone	Team3URL

Team	First Address (combined with First Ste/Bldg)	First City	First Phone	Second Address (combined with Second Ste/Bldg)	Second City	Second Phone	Third Address (combined with Third Ste/Bldg)	Third City	Third Phone	URL
Team1	Store1Address, Ste A	Store1City	Store1Phone	Store2Address	Store2City	Store2Phone				Team1URL
Team 2	Store3Address, Bldg 2	Store3City	Team2Phone	Store4Address	Store4City		Store5Address	Store5City		Team2URL
Team 3	Store6Address, Unit 5	Store6City	Store6Phone	Store7Address	Store7City	Store7Phone	Store8Address	Store8City	Store8Phone	Team3URL

 Here is an example of the above tables as arrays.

_________Original Data___________

[

  {

    "Store ID": "13548",

    "Team ID": "1",

    "Address": "Store1Address",

    "Suite/Bldg": "Ste A",

    "City": "Store1City",

    "Phone": "Store1Phone",

    "URL": "Team1URL"

  },

  {

    "Store ID": "64815",

    "Team ID": "1",

    "Address": "Store2Address",

    "Suite/Bldg": "",

    "City": "Store2City",

    "Phone": "Store2Phone",

    "URL": "Team1URL"

  },

  {

    "Store ID": "45822",

    "Team ID": "2",

    "Address": "Store3Address",

    "Suite/Bldg": "Bldg 2",

    "City": "Store3City",

    "Phone": "Team2Phone",

    "URL": "Team2URL"

  },

  {

    "Store ID": "44782",

    "Team ID": "2",

    "Address": "Store4Address",

    "Suite/Bldg": "",

    "City": "Store4City",

    "Phone": "Team2Phone",

    "URL": "Team2URL"

  },

  {

    "Store ID": "69732",

    "Team ID": "2",

    "Address": "Store5Address",

    "Suite/Bldg": "",

    "City": "Store5City",

    "Phone": "Team2Phone",

    "URL": "Team2URL"

  },

  {

    "Store ID": "87385",

    "Team ID": "3",

    "Address": "Store6Address",

    "Suite/Bldg": "Unit 5",

    "City": "Store6City",

    "Phone": "Store6Phone",

    "URL": "Team3URL"

  },

  {

    "Store ID": "48676",

    "Team ID": "3",

    "Address": "Store7Address",

    "Suite/Bldg": "",

    "City": "Store7City",

    "Phone": "Store7phone",

    "URL": "Team3URL"

  },

  {

    "Store ID": "99949",

    "Team ID": "3",

    "Address": "Store8Address",

    "Suite/Bldg": "",

    "City": "Store8City",

    "Phone": "Store8phone",

    "URL": "Team3URL"

  }

]

_____________Array of combined Stores as teams_________

[

  {

    "Team": "Team1",

    "First Address (combined with First Ste/Bldg)": "Store1Address, Ste A",

    "First City": "Store1City",

    "First Phone": "Store1Phone",

    "Second Address (combined with Second Ste/Bldg)": "Store2Address",

    "Second City": "Store2City",

    "Second Phone": "Store2Phone",

    "Third Address (combined with Third Ste/Bldg)": "",

    "Third City": "",

    "Third Phone": "",

    "URL": "Team1URL"

  },

  {

    "Team": "Team 2",

    "First Address (combined with First Ste/Bldg)": "Store3Address, Bldg 2",

    "First City": "Store3City",

    "First Phone": "Team2Phone",

    "Second Address (combined with Second Ste/Bldg)": "Store4Address",

    "Second City": "Store4City",

    "Second Phone": "",

    "Third Address (combined with Third Ste/Bldg)": "Store5Address",

    "Third City": "Store5City",

    "Third Phone": "",

    "URL": "Team2URL"

  },

  {

    "Team": "Team 3",

    "First Address (combined with First Ste/Bldg)": "Store6Address, Unit 5",

    "First City": "Store6City",

    "First Phone": "Store6Phone",

    "Second Address (combined with Second Ste/Bldg)": "Store7Address",

    "Second City": "Store7City",

    "Second Phone": "Store7Phone",

    "Third Address (combined with Third Ste/Bldg)": "Store8Address",

    "Third City": "Store8City",

    "Third Phone": "Store8Phone",

    "URL": "Team3URL"

  }

]

[v-bofeng-msft · Accepted solution]

Hi @blairing26 ,

I've made a test for your reference:

1\

item()['Team ID']

2\

union(body('Select'),body('Select'))

3\

item()['Team ID']

4\

items('Apply_to_each')['Team ID']

5\

{
"Team": @{body('Filter_array')?[0]?['Team ID']},
"First Address (combined with First Ste/Bldg)":@{body('Filter_array')?[0]?['Address']},
"First City": @{body('Filter_array')?[0]?['City']},
"First Phone":@{body('Filter_array')?[0]?['Phone']},
"Second Address (combined with Second Ste/Bldg)": @{body('Filter_array')?[1]?['Address']},
"Second City": @{body('Filter_array')?[1]?['City']},
"Second Phone": @{body('Filter_array')?[1]?['Phone']},
"Third Address (combined with Third Ste/Bldg)": @{body('Filter_array')?[2]?['Address']},
"Third City": @{body('Filter_array')?[2]?['City']},
"Third Phone": @{body('Filter_array')?[2]?['Phone']},
"URL": @{body('Filter_array')?[0]?['URL']}
}

Result:

Best Regards,
Bof

[blairing26]

Thank you Thank you Thank you!!! I've been trying to figure that out for sooo long.  It is working perfectly.  However, I ran into another problem with the same data source.  In addition to the columns I listed above, there are columns for owners for each store.  There is a primary owner for each location and some with a secondary owner or third owner.  It

Store ID	Team ID	Owner1	Owner2	Owner3
12345	33	John Doe	Cathy Smith
23456	33	Cathy Smith	John Doe	Fred Wilson
34567	33	Fred Wilson

What I need is a way to get the unique name values from each column for all rows in a group so that the end result would be

Store ID	Team ID	Owner1	Owner2	Owner3
	33	Cathy Smith	Fred Wilson	John Doe

I have tried numerous ways to get the values from the multiple columns AND multiple rows into one row, containing only the unique names from those columns and rows, but nothing has made sense. 

I'm open to it being two separate flows that I then combine into one data file... The initial output with the Team ID and the addresses and the second output with the Matching Team ID & the Store Owners.  Thoughts? 

[v-bofeng-msft]

Hi @blairing26 ,

I really understand that you have more than one issue to be solved today. And I'm surely happy to help you with these, but to better focus on the new problem and ensure the service quality, we would advise you to create one post with one issue.

Thanks for your understanding.

Best Regards,

Bof

[blairing26]

Totally understand!  Thank you.  I'll do that.