cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Silva_BR
Helper III
Helper III

Change Collection

Hi Guys! I have a situation and I need ideas, thank you in advance!

Problem: I have a collection built like this:

IDForm      Question       Answer   
1                  Name          John
1                  Profession  Lawyer

1                  Material      Metall



Need the "Collection of Lines" above to look like this:

IDForm             Question_1                Answer_1          Question_2              Answer_2
1                         Name                       John                   Profession                Lawyer

Important: Each form is different according to the IDForm, there are forms with 30 questions and answers and another IDForm with 120 questions and answers.The "Collection of Rows" needs to go through all the questions and answers and create the columns according to the number of questions in each form.This collection will be exported to Excel with this data, so each IdForm will be a row in this Excel. The problem is that each IDForm is a specific form with different number of questions that the user creates, the solution needs to be dynamic to create N number of columns in this new row collection.Does anyone have any idea how to help me?

Ty

2 ACCEPTED SOLUTIONS

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

As @WarrenBelz said, you cannot have different number of columns depending on the input - the schema of the tables in Power Apps needs to be known in advance. You can, however, have a large number of columns (you can choose the maximum number that you want to support), and leave many of them with blank values if the corresponding row does not have enough questions/answers.

 

The expression that you would use to create this table is long, but easy. Here is an example for a maximum of 8 questions/answers - extending for more should be trivial.

DropColumns(
    AddColumns(
        GroupBy(coll, "IDForm", "ById"),
        "Question_1", If(CountRows(ById) >= 1, Last(FirstN(ById, 1))).Question,
        "Answer_1", If(CountRows(ById) >= 1, Last(FirstN(ById, 1))).Answer,
        "Question_2", If(CountRows(ById) >= 2, Last(FirstN(ById, 2))).Question,
        "Answer_2", If(CountRows(ById) >= 2, Last(FirstN(ById, 2))).Answer,
        "Question_3", If(CountRows(ById) >= 3, Last(FirstN(ById, 3))).Question,
        "Answer_3", If(CountRows(ById) >= 3, Last(FirstN(ById, 3))).Answer,
        "Question_4", If(CountRows(ById) >= 4, Last(FirstN(ById, 4))).Question,
        "Answer_4", If(CountRows(ById) >= 4, Last(FirstN(ById, 4))).Answer,
        "Question_5", If(CountRows(ById) >= 5, Last(FirstN(ById, 5))).Question,
        "Answer_5", If(CountRows(ById) >= 5, Last(FirstN(ById, 5))).Answer,
        "Question_6", If(CountRows(ById) >= 6, Last(FirstN(ById, 6))).Question,
        "Answer_6", If(CountRows(ById) >= 6, Last(FirstN(ById, 6))).Answer,
        "Question_7", If(CountRows(ById) >= 7, Last(FirstN(ById, 7))).Question,
        "Answer_7", If(CountRows(ById) >= 7, Last(FirstN(ById, 7))).Answer,
        "Question_8", If(CountRows(ById) >= 8, Last(FirstN(ById, 8))).Question,
        "Answer_8", If(CountRows(ById) >= 8, Last(FirstN(ById, 8))).Answer),
    "ById")

The attached app shows this formula being used. To open it, save it locally, then go to https://create.powerapps.com, select Open -> Browse, and find the file that you saved.

 

Hope this helps!

View solution in original post

Silva_BR
Helper III
Helper III

Ty, i'm successful my formula

ClearCollect(
Col_01;
AddColumns(
GroupBy(
AddColumns(
Col_Exemplo;
"Coluna";
Pergunta & ";" & Resposta;
"ID";
IDForm
);
"IDForm";
"ITEMS"
);
"Linha";
IDForm & ";" & Concat(
ITEMS;
Coluna;
";"
);
"Total";
CountRows(ITEMS)
)
)



Pls Changer (power apps in pt-br  -> en-us)
; -> , 
and

;; -> ;

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

HI @Silva_BR ,

Sorry, not possible as you cannot refer dynamically to column names to create them (you must state the intended name)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

CarlosFigueira
Power Apps
Power Apps

As @WarrenBelz said, you cannot have different number of columns depending on the input - the schema of the tables in Power Apps needs to be known in advance. You can, however, have a large number of columns (you can choose the maximum number that you want to support), and leave many of them with blank values if the corresponding row does not have enough questions/answers.

 

The expression that you would use to create this table is long, but easy. Here is an example for a maximum of 8 questions/answers - extending for more should be trivial.

DropColumns(
    AddColumns(
        GroupBy(coll, "IDForm", "ById"),
        "Question_1", If(CountRows(ById) >= 1, Last(FirstN(ById, 1))).Question,
        "Answer_1", If(CountRows(ById) >= 1, Last(FirstN(ById, 1))).Answer,
        "Question_2", If(CountRows(ById) >= 2, Last(FirstN(ById, 2))).Question,
        "Answer_2", If(CountRows(ById) >= 2, Last(FirstN(ById, 2))).Answer,
        "Question_3", If(CountRows(ById) >= 3, Last(FirstN(ById, 3))).Question,
        "Answer_3", If(CountRows(ById) >= 3, Last(FirstN(ById, 3))).Answer,
        "Question_4", If(CountRows(ById) >= 4, Last(FirstN(ById, 4))).Question,
        "Answer_4", If(CountRows(ById) >= 4, Last(FirstN(ById, 4))).Answer,
        "Question_5", If(CountRows(ById) >= 5, Last(FirstN(ById, 5))).Question,
        "Answer_5", If(CountRows(ById) >= 5, Last(FirstN(ById, 5))).Answer,
        "Question_6", If(CountRows(ById) >= 6, Last(FirstN(ById, 6))).Question,
        "Answer_6", If(CountRows(ById) >= 6, Last(FirstN(ById, 6))).Answer,
        "Question_7", If(CountRows(ById) >= 7, Last(FirstN(ById, 7))).Question,
        "Answer_7", If(CountRows(ById) >= 7, Last(FirstN(ById, 7))).Answer,
        "Question_8", If(CountRows(ById) >= 8, Last(FirstN(ById, 8))).Question,
        "Answer_8", If(CountRows(ById) >= 8, Last(FirstN(ById, 8))).Answer),
    "ById")

The attached app shows this formula being used. To open it, save it locally, then go to https://create.powerapps.com, select Open -> Browse, and find the file that you saved.

 

Hope this helps!

@Silva_BR ,

@CarlosFigueira is correct and I should have extended my answer saying you need to hard-code the field names. Be aware that you would need to know the number of questions you are going to process.

Silva_BR
Helper III
Helper III

Ty, i'm successful my formula

ClearCollect(
Col_01;
AddColumns(
GroupBy(
AddColumns(
Col_Exemplo;
"Coluna";
Pergunta & ";" & Resposta;
"ID";
IDForm
);
"IDForm";
"ITEMS"
);
"Linha";
IDForm & ";" & Concat(
ITEMS;
Coluna;
";"
);
"Total";
CountRows(ITEMS)
)
)



Pls Changer (power apps in pt-br  -> en-us)
; -> , 
and

;; -> ;

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

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