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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,442)