cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerSoumyo
Regular Visitor

Transposing and Merging Collections

I am working on a solution for one of our clients where I need to merge a collection with transpose of a row in another collection as explained below:

 

PowerSoumyo_0-1624347021516.png

 

The two tables on the left represent the two collections that I already have, storing the question data and another one storing the responses to those questions by each user. When admin users log in to the app, they see a gallery of responses (just a title, not the detailed responses) and when they click on a title, the detailed responses from collection 2 will need to be displayed in the format shown in the output collection above. Let me know if this will be feasible or will the solution needs to be designed differently. Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Alex_10
Super User
Super User

@PowerSoumyo 

If Collection2 is a data source for admin gallery (lets call adminGallery):

 

adminGallery.OnSelect = 

Clear(colCollection1_2);
ForAll(colCollection1 As ThisQues,
    Collect(colCollection1_2,
    {
    'QuesID': ThisQues.QuesID,
    'Ques': ThisQues.Ques,
    'Response': Switch(ThisQues.QuesID,
    "Q1", ThisItem.Q1,
    "Q2", ThisItem.Q2,
    "Q3", ThisItem.Q3,
    "Q4", ThisItem.Q4,
    "Q5", ThisItem.Q5
    )})
)

 

 

create a new gallery (lets call resultGallery):

resultGallery.Items = colCollection1_2

 

 

View solution in original post

5 REPLIES 5
Alex_10
Super User
Super User

@PowerSoumyo 

If Collection2 is a data source for admin gallery (lets call adminGallery):

 

adminGallery.OnSelect = 

Clear(colCollection1_2);
ForAll(colCollection1 As ThisQues,
    Collect(colCollection1_2,
    {
    'QuesID': ThisQues.QuesID,
    'Ques': ThisQues.Ques,
    'Response': Switch(ThisQues.QuesID,
    "Q1", ThisItem.Q1,
    "Q2", ThisItem.Q2,
    "Q3", ThisItem.Q3,
    "Q4", ThisItem.Q4,
    "Q5", ThisItem.Q5
    )})
)

 

 

create a new gallery (lets call resultGallery):

resultGallery.Items = colCollection1_2

 

 

View solution in original post

Thanks for your help @Alex_10 

So, this means whenever a new question is added to the data source, my app will need to be updated in the Switch part of the formula, correct? Is there a way to make that automatic?

Alex_10
Super User
Super User

@PowerSoumyo 

yes, you are right.

to make it in auto mode, you need to redesign COllection2 - each question answer has to be a separate column like:

 

PersonID              QuesID     Answer

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

John_Doe_ID          Q1            yes

John_Doe_ID          Q2            no

Jane_Doe_ID          Q1             no

@Alex_10 Thanks again So, which one of these designs (top or the bottom one) do you think will be better:

 

PowerSoumyo_0-1624462592009.png

 

And if I go with the second design that you suggested (bottom), will the following formula work to merge the two collections and get the desired output as per my requirement:

 

ClearCollect(newCol, Addcolumns(Col2,"ques",lookup(col1,QuesID=col2[@Question]).Ques))

Alex_10
Super User
Super User

@PowerSoumyo 

with the bottom collection that code should work in general.

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 (1,345)