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

Unpivot columns in a SP list into a PowerApps collection

Hi good folks, I have a challenge I need help with

 

I have a Sharepoint List structured like this:

 

NameApplesPearsOranges
John58652654
Todd65434354
Emily654341458

 

In PowerBI or Excel I'm able to unpivot this table to look like this:

 

NameFruitValue
JohnApples58
JohnPears652
JohnOranges654
ToddApples654
ToddPears34
ToddOranges354
EmilyApples654
EmilyPears341
EmilyOranges458

 

Now I would like to make a collection in PowerApps - from this Sharpoint List - that looks like the 2nd table.

 

Any thoughts?

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @Larsvedoy ,

 

Please try this code:

 

ClearCollect(FruitCategory,["Apples","Pears","Oranges"]);Clear(MyCol);ForAll('SP list',ForAll(FruitCategory,Collect(MyCol,If(Value="Apples",{Name:Name,Fruit:"Apples",Value:Apples},Value="Pears",{Name:Name,Fruit:"Pears",Value:Pears},Value="Oranges",{Name:Name,Fruit:"Oranges",Value:Oranges}))))

 

Test Result:

Snipaste_2020-04-03_14-09-32.png

Sik

View solution in original post

3 REPLIES 3
v-siky-msft
Community Support
Community Support

Hi @Larsvedoy ,

 

Please try this code:

 

ClearCollect(FruitCategory,["Apples","Pears","Oranges"]);Clear(MyCol);ForAll('SP list',ForAll(FruitCategory,Collect(MyCol,If(Value="Apples",{Name:Name,Fruit:"Apples",Value:Apples},Value="Pears",{Name:Name,Fruit:"Pears",Value:Pears},Value="Oranges",{Name:Name,Fruit:"Oranges",Value:Oranges}))))

 

Test Result:

Snipaste_2020-04-03_14-09-32.png

Sik

View solution in original post

Thank you @v-siky-msft ,

 

This worked like a charm!

 

Lars

 

Hei 

It's working fine for just displaying the collection in the gallery. What if I want to add the text box for each fruit (last column)in the gallery and patch value from that text box to the data source . 
How can I achieve that ?? Any tips would be appreciated.

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,925)