Transposing data in dataverse table

[Sarimshah91]

Hi All I am working on a powerapp that uses SQL connectors to pull data in real time based on a user input. All of this data pulled from SQL database is than stored in collections to be submitted later using a form in the app. I am using concat functions to write data into the field within a form i.e. MCHrequestDetails is the collection Name, Article is the column that I am concatenating. ScreenShot Current Outcomes shows how it shows up in the Model Driven App (exported as excel). As you can see it is very hard to read and present serious data mismatch risks. I want to transform this data to look something similar to attached screenshot (Desired Outcome). As we can see from the screenshots attached that all the comma separated entries for one request are now shown up as different rows rather than jumbled up in one row.  Thanks Regards Sarim

[EddieE]

@Sarimshah91 

Unfortunately, transpose isn't a function in PowerApps - I (and many others) wish it was though!

However, recently I built a short script that transposes 1 record, which you may be able to adapt for many records, possibly using ForAll()? Here is how I did it using the JSON() function 

// Get single record
Set( sapData, First( myDataSource));

// convert SAP Data to JSON text string, removing unwanted characters
Set(
    vJSONData, 
    Substitute(
        Substitute(
          Substitute(
                Substitute(
                    Substitute(
                        JSON( sapData)
                        ,
                        """", ""
                    ),
                "[", ""
                ),
            "]", ""
            ),
        "{", ""
        ),
    "}", ""
    )
);

// Transpose JSON string into a two column table
// The Split below works on key value pairs, or something similar
Clear(colTranspose);
ForAll(
    Split( vJSONData, ",") As _data,
    Collect(
        colTranspose,
        {
            column1: First(Split( _data.Value, ":")).Value,
            column2: Last(Split( _data.Value, ":")).Value
        }
    )
);

The above is also dynamic, in that if the record gets more/less and/or new columns the transpose adjusts to suit.

Like I said, this only works on 1 record and I haven't bothered to look into extending it for more than 1 at this stage ... plus I really don't have the time right now to spend on that. 

Hopefully this can help?

[Sarimshah91]

Hi EddieE

Thanks for your quick response, just for my understanding, how would this code convert the entries separated by comma in one row into multiple rows and secondly assuming it does, if I use the same collection to write into a dataverse table, would it than show up just as one record with one row or one record with multiple rows? 
Please see the desired outcome screenshot that I attached. 
Thank you so much for your help and I really appreciate it

[EddieE]

Hey @Sarimshah91 

As I mentioned above, I don't really have the time right now to spend on extending this code. As it is written, it can take a single record (so therefore 2 row ie column headers + the record data) and transpose that into a two column table with the column headers in the first column of the new table and the matching record data in the second column.

To extend this beyond that (into your Desired Outcome) looks to be a challenge and I'm not even sure if it's possible.

To see what's going on in the above code you can add a label and set it's text to vJSONData. Plus, if you comment out all of the Substitute() code (ie just use this Set( vJSONData, JSON (sapData)) ) you'll get an idea of what I'm doing. I mentioned the key-value pairs because when you use Split() that's what it's separating off. Then I just use the First() and Last() parts of that text string for each row in the Split() table.

Sorry I can't be of more help