Change Collection

[Silva_BR]

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

[WarrenBelz]

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 · Accepted solution]

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!

[WarrenBelz]

@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 · Accepted solution]

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

;; -> ;