cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sasrsc
Resolver II
Resolver II

Group by from detail tables....(complex) then merge to create one master table

My goal is to have one row per attendee with info derived from numerous tables. I need a sanity check to get me kick started here...

Master Table (parent) = Attendee
One row per attendee
Mickey Mouse - VIP - Mickey.Mouse@disney.com
Minnie Mouse - VIP - Minnie.Mouse@disney.com
Mr. Villain - - badguy@acme.com

Child Table = Transport
One row per flight per attendee 
Typically that means 2 rows (if someone flies non stop)
Mickey Mouse - ATL to MCO - 24DEC - AA 123
Mickey Mouse - MCO to ATL - 26DEC - AA 567
Mickey Mouse - LON to MCO - 23DEC - DL 53
Mickey Mouse - MCO to LON - 27DEC - DL 5
Mr. Villain - EWR to MCO - 24DEC - WN 22
Mr. Villain - MCO to EWR- 27DEC - WN 361

Child Table = Hotels
One row per hotel booking per attendee
Mickey Mouse - Check in 24DEC - Check Out 26DEC - The Grand Floridian - Suite - $500
Minnie Mouse - Check in 23DEC - Check Out 27DEC - The Polynesian - ROH- $250
Mr. Villain - - Check in 24DEC - Check Out 25DEC - BadHotel - Broom Closet- $50
Mr. Villain  - Check in 25DEC - Check Out 27DEC - The Secret Hotel - Corner Suite- $99


What I need to do is get the one row for each attendee and bring into that row the min check in date of their hotel(s), the max check out date of their hotel(s) - Notice that Mr. Villain has 2 hotels so I want to return 24DEC and 27DEC which is the min/max of "all his rooms". Then I want to combine into this the flight arrival date into MCO and the flight departure date from MCO for each person. 

Why - you ask?
I'm performing quality control to make sure that everyone has a hotel for every night that they are staying in Orlando based on when the arrive and depart. Make sense?

The data source is SP Lists right now. 

Right now, I'm planning on trying to create a collection with the hotel data, a collection with the flight data and then add columns to the master attendee table. I'm struggling a little ... advice welcome...








1 ACCEPTED SOLUTION

Accepted Solutions
VijayTailor
Resident Rockstar
Resident Rockstar

Hi @sasrsc ,

You can use the approach like below.

ClearCollect(colStudentClassMap, 
     AddColumns(
          StudentClassMap,
          "Class", Filter(Class, ClassId=StudentClassMap[@ClassId]),
          "User", Filter(User, UserId=StudentId)
     )
)

 Thanks,

Vijay

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."-Vijay

View solution in original post

2 REPLIES 2
VijayTailor
Resident Rockstar
Resident Rockstar

Hi @sasrsc ,

You can use the approach like below.

ClearCollect(colStudentClassMap, 
     AddColumns(
          StudentClassMap,
          "Class", Filter(Class, ClassId=StudentClassMap[@ClassId]),
          "User", Filter(User, UserId=StudentId)
     )
)

 Thanks,

Vijay

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."-Vijay

View solution in original post

Thanks Vijay... that just helps reduce the number of steps. 
It's good to learn with baby steps but then combine when you grasp the concepts like this (as it reduces the code and makes it more efficient).

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,096)