Transpose 2-column Collection into single row - with column names from ID column items

(0) Share

Report

Posted on by jatcube

508

Hi all,

Scenario:

I have a Canvas app where I created a dynamically populated gallery that lets users type in various bits of info - essentially a form with dynamically changing fields. I then store the results in a collection using the below code, which is great, but my results are stored in 2 columns.

The end aim here is to be able to export the data to power automate so an xml schema can be inserted into a word file.

How do I transpose them to be a single row table with the item names in the ID column to be my column names? My problem is, the names and the number of items keep changing. I couldn't find a transpose function in powerapps (which I definitely took for granted in Excel).

I'm aware I'll probably have to rethink, but I'm pretty stuck with all this data manipulation in PowerApps

Any ideas? Thanks so much

Clear(tempCollection);
ForAll(
 GalleryDynamicItems.AllItems,
 Collect(
 tempCollection,
 {
 id: ID,
 Value: If(
 Type = "Textbox",
 TextInput1.Text,
 Type = "Dropdown",
 Dropdown1.Selected.Result,
 DatePicker1.SelectedDate
 )
 }
 )
);

This is the result:

What I want is this:

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

BrianS 2,407 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

I did not try this but it might work -

you could use a ForAll to loop thru the collection and create a second collection that used the fields to create the columns and the Field names as the values. You would want to be careful that the reponses will be valid names

Was this reply helpful? Yes No
Verified answer

jatcube 508 on at

Like (0)

Report
Copy link

Link copied!

Thanks @BrianS for the response. I tried many ways to dynamically pass a column name, but I could not manage to make it work, I don't think it's an option yet.

That said, it turns out I was stuck because I was going down the rabbit hole.. I just needed to realise that I can use JSON(mycollection) to pass the entire collection to power automate where I used the parse json connector in a for each loop on the value and Id pair to build the XML map.

In hindsight, dead easy, but got me spinning for a few days...

Was this reply helpful? Yes No