Transpose Data Collection

[Pegger]

I am trying to transpose a data collection.

Original Collection Format (Orig_Collection)

      Q1             Q2

   Agree        Disagree

Target Collection Format (Target_Collection)

      Question     Response

         Q1              Agree

         Q2           Disagree

ClearCollect(Target_Collection,
{Question: "Q1", Response:Orig_Collection.Q1},

{Question: "Q2", Response:Orig_Collection.Q2} );

I am performing this action on the OnVisible property for the screen.  My problem is that the Response column in the Target Collection is showing the values in a nested table.  I just want the response to show as text (either "Agree" or "Disagree").

[Anonymous · Accepted solution]

@Pegger 

Transpose = yuck! in PowerApps, but this does it for you. Note, this will only work on a table with one record. You may need to adjust it for tables with more than one record ... that's a project for you 🙂

Clear(Target_Collection);
ForAll(

    // create table of records in JSON format and substitute out the unneeded Chars
    Split(
        Substitute(
            Substitute(
                Substitute(
                    Substitute(
                        Substitute(
                            JSON(
                                Orig_Collection,
                                JSONFormat.IgnoreBinaryData
                            ),
                            "[",
                            ""
                        ),
                        "{",
                        ""
                    ),
                    "}",
                    ""
                ),
                "]",
                ""
            ),
            Char(34),
            ""
        ),
        ","
    ),
    
    // Transpose data
    Collect(
        Target_Collection,
        {
            Question: Left(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) - 1
            ),
            Response: Mid(
                ThisRecord.Result,
                Find(
                    ":",
                    ThisRecord.Result
                ) + 1
            )
        }
    )
)