Consolidate multiple column questions into a question column with answers and comments

Question

I am currently trying to create an auditing application for report writing findings from a series of Sharepoint tables featuring a series of question columns (Choice type column) and associated comments columns. The tables look like this:

Question 1	Comment 1	Question 2	Comment 2	Question 3	Comment 3	AuditID
Completed	n/a	Not Completed	Missing files	Not Completed	No Signature	1

For ease of use for the end user (the auditor), I wanted to create a table or gallery that displays only the questions with findings per list(I can create separate galleries per list and have the user tab between them, so no need to combine multiple Sharepoint lists)

I wanted to display it like this:

Questions	Answers	Comments	AuditID
Question 1	Completed	n/a	1
Question 2	Not Completed	Missing Files	1
Question 3	Not Completed	No Signature	1

I wanted to try and leverage Collections in Powerapps to create this visual without needing to store it somewhere permanently. That would be ideal, but if the only solution is to somehow append the results to a separate table then that's what I'll go with. Any insight on achieving the second table and displaying it into a gallery or table in Powerapps that is able to be filtered by "Not Completed" entries is greatly appreciated.

In attempting to find articles that would answer this, I've come to realize that I could not figure out how to best phrase this issue without having a fully realized question here, but rest assured I did as much digging as I possibly could before I came to ask this here.

Again, all help to find a solution for this is greatly appreciated.

Answer 1

You can use an expression similar to the one below to "expand" the three questions/answers into individual rows from your table:

Ungroup(
    ForAll(
        MySPTable,
        {
            Rows: [
                {
                    Questions: "Question1",
                    Answers: Question1,
                    Comments: Comment1,
                    AuditID: AuditID
                },
                {
                    Questions: "Question2",
                    Answers: Question2,
                    Comments: Comment2,
                    AuditID: AuditID
                },
                {
                    Questions: "Question3",
                    Answers: Question3,
                    Comments: Comment3,
                    AuditID: AuditID
                }
            ]
        }
    ),
    Rows
)

The idea is to use the ForAll function to map each row of the SharePoint table into a record with a single field (Rows) which contains a 3-element table. That result is then fed into the Ungroup function, which would then "hoist" those nested rows into the outer table.