How to pivot a single-record Collection?

(0) Share

Report

Posted on by seadude

1,855

I have a single-record Collection. I want to display this record in a Gallery with all values shown.

Example:

- Collection:

ClearCollect(colTest,
 {
 id: 1, text: "test", userName: "Guy"
 }
)

- Pivot pseudo-code:

ForEveryColumn in colTest,
 Collect(colTest2,
 {
 columnName: ThisRecord.?,
 columnValue: ThisRecord.?
 }
 )
)

- Then in the Gallery:

- Items property: colTest2

- Labels set to: columnName and columnValue

Ideas on how to do this? I want to basically take a record in a Collection and turn it to another Collection of key-value pairs.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

SanmeshG 1,947 Moderator on at

Like (0)

Report

Hi @seadude ,

Please see if the below article helps.

http://powerappsguide.com/blog/post/transpose-convert-rows-to-columns

Thanks!

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (3)

Report
Hi @seadude

I assume the column names in your collection are dynamic. Therefore, the approach I would take is to convert the collection to JSON. You can then use string manipulation functions on the raw JSON to extract the key/value pairs.

In the formula beneath, I build a temp collection to simplify the parsing but I'm sure you could adapt this if you prefer not to have that.

ClearCollect( colTest, { id: 1, text: "test", userName: "Guy" } ); Set( varJSON, JSON(colTest) ); ClearCollect( colTemp, ForAll( Split( Match( varJSON, "\[\{(?<Result>.*)\}\]" ).Result, "," ), {columnName:First(Split(Value, ":")), columnValue:Last(Split(Value, ":")) } ) ); ClearCollect( colTest2, ForAll(colTemp, { columnName:Substitute(columnName.Value,"""",""), columnValue:Substitute(columnValue.Value, """","")} ) )

Was this reply helpful? Yes No
seadude 1,855 on at

Like (0)

Report

Hi @timl !!

Man! We used to chat a LOT on here! haahh! Nice. Thanks for hollering!

This is a very sophisticated suggestion! Wow! I hadn't thought about parsing JSON within PowerApps like this!

RE: "I assume the column names in your collection are dynamic":
- The column names will be the same each time
- Just a single record collection that is being "collected into" throughout user experience
- Use Case:
- A blank Collection is created `OnStart` of the application using `FirstN(datasource, 1)` that is subsequently `ClearCollect(colNewSurvey)`'d then `Clear(colNewSurvey)`'d
- As the user selects values from Dropdown/Comboboxs, and enters data into Textbox Controls, they are Collected into a single record in `colNewSurvey`
- The goal was to take this single record and transpose it to a key-value Collection that I could visualize in a single Gallery for final review before submitting `colNewSurvey`.
- Currently on the `scrReview` Screen, I have a bunch of Label Controls set to things line `First(colNewSurvey).Column1`, `First(colNewSurvey).Column2`, etc.
- This adds a bunch of (what I deem) unnecessary Controls to the app.

I'll study your suggestion and see how it can be applied to static columns.

THank yoU!

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report
Hi @seadude

Just following up on this. Did you get anywhere with the above?

On the basis that the column names are always the same, the alternative would be to hardcode the column names like so. Perhaps that might be an easier solution?

Table( {columnName: "id", columnValue:Text(First(colTest).id)}, {columnName: "text", columnValue:First(colTest).text}, {columnName: "username", columnValue:First(colTest).userName} )

Was this reply helpful? Yes No