Converting concatenated text to columns in a table

Question

So, I've inherited this beast of a PowerApp that does something relatively simple, collects responses to dynamic quizzes created in the app. The operator creates questions (which adds items to a SPO list) and then sends the quiz out to folks who then go and answer it in the PowerApp.

Because the questions are created more or less on a whim and because PowerApps can't create new data sources on the fly or manipulate the data sources it uses, instead of having a column for every question/answer pair, those QnA pairs are concatenated into a giant blob of plain text and shoved into a single column in a separate SPO list holding response in the format:

Question: Question1 Text? Answer: Answer1 Text | Question: Question2 Text? Answer: Answer2 Text, etc., etc.

I've figured out the regex I need to split the block into QnA pairs (split on the bar character) and then each QnA pair into, essentially, a 2D array (in JavaScript, anyway, I'd split on the regex /((\?|\.)( Answer: ))/ because of course a question can end on either a period or a question mark).

What I can't quite figure out because PowerApps' function engine is bizarre and byzantine, is how to use that regex to turn that blob into a table I can output into the app so my operator can extract the data into something more useful.

Edit 1:

Getting a little closer. With the following function I can get a 2D collection:

ClearCollect(
    QnAPairs,
    ForAll(
        'ResponsesList',
        Split(
           'ResponsesList'[@Answers],
           "|"
        )
    )
);

Still at a loss on how to treat each result in the QnA collection like key/value pairs I can add to a fresh table.

TL;DR?

How to turn

Name            | Date     | Answers
Person McPerson | mm/dd/yy | Question: Question1 Text? Answer: Answer1 Text | Question: Question2 Text? Answer: Answer2 Text, etc., etc.

Into

Name            | Date     | Question1 Text | Question2 Text | ...QuestionN Text
Person McPerson | mm/dd/yy | Answer1 Text   | Answer2 Text   | ...Answer2 Text

carlosfigueira carlosfigueira · Accepted Answer · 2020-08-16T15:26:16

If you want to display the questions/answers to the operator (for example, in a gallery), then you need a structure that has the questions as rows instead of columns, so your structure would be somewhat like this:

Name            | Date       | QuestionsAndAnswers
Person McPerson | mm/dd/yyyy | <table>

Where <table> would have the individual questions and answers as rows. You can create that new structure with an expression like this:

    ClearCollect(
        QnAPairs,
        DropColumns(
            AddColumns(
                ResponsesList,
                "QuestionsAndAnswers",
                MatchAll(Answers, "Question:\s?(?<question>[^\?]+)\?\s?Answer\s?:\s?(?<answer>[^|]+)")),
            "Answers"))

So in your app if you have a gallery that lists all the respondents with the Items property set to QnAPairs, you can have a second gallery with the Items property set to Gallery1.Selected.QuestionsAndAnswers, and it will show the Q&A for the selected person.

Notice that you don't need to have a separate collection; if you set the Items property of the first gallery to your original data source (ResponseList), you can have a second gallery with the Items property set to

MatchAll(
    Gallery1.Selected.Answers,
    "Question:\s?(?<question>[^\?]+)\?\s?Answer\s?:\s?(?<answer>[^|]+)")

And it will show the answers for the selected person.

You can find an app that shows this strategy at https://carlosfigueirapastorage.blob.core.windows.net/public/StackOverflow63425784.msapp. To open it, save it locally, then go to https://create.powerapps.com, select Open, Browse, and find the file that you saved.

Converting concatenated text to columns in a table

1 Answers