cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pegger
Helper I
Helper I

Transpose Data Collection

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").

1 ACCEPTED SOLUTION

Accepted Solutions
Eelman
Super User
Super User

@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
            )
        }
    )
)

 

 

 

View solution in original post

1 REPLY 1
Eelman
Super User
Super User

@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
            )
        }
    )
)

 

 

 

View solution in original post

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,553)