web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Transposing table from...
Power Apps
Answered

Transposing table from single rows to columns

(0) ShareShare
ReportReport
Posted on by 20

Hello friends,

i have a question where i do not seem to get anywhere with my attempts or with what i read up in the forums here. I have the case, that i have to use datasources from custom dataverse tables that are created like this:

 

Category	| Fieldname		| Value				| DatasetNo
===========================================================
Employee	| Name			| Peter Parker		| 1
Employee	| Company		| SomeCompany1		| 1
Employee	| Email			| peter@parker.com	| 1
Employee	| Name			| Clark Kent		| 2
Employee	| Company		| SomeCompany2		| 2
Employee	| Email			| clark@kent.com	| 2
Employee	| Name			| Tony Stark		| 3
Employee	| Company		| SomeCompany3		| 3
Employee	| Email			| tony@stark.com	| 3

 

As you can see, each employee from the list has 3 single rows of data, where DatasetNo is the key. I have these values in a collection and need to display them (e. g. in a Listbox), so i wonder how i would smartly 'convert' the collection to something this:

 

EmployeeName	| EmployeeCompany	| EmployeeEmail		| DatasetNo
===================================================================
Peter Parker	| SomeCompany1		| peter@parker.com 	| 1
Clark Kent		| SomeCompany2		| clark@kent.com	| 2
Tony Stark		| SomeCompany3		| tony@stark.com	| 3

 

So that all values belonging to one dataset are like transfered to a new collection i could like easily use in a Listbox or any component.

I've tried some things myself, like making 4 seperate collections - one for each column - and combine them afterwards. But that does not seem like the smart way to handle it. Maybe you have a smarter way to do that?

Categories:
I have the same question (0)
  • skoerber Profile Picture
    20 on at

    Hi Madlad,

    thanks a lot for taking the time to help out with this. This absolutely works like described. And i am coming back to this so late, because i had different other challenges for this problem. You where giving me great ideas, on how to use different functions, so this was very helpful. Thanks again for that!

    S.

  • Verified answer
    madlad Profile Picture
    2,637 Moderator on at

    The easiest way to get this would be to use  Grouping/Ungrouping - however, this wouldn't get it perfectly like you requested, but it should be able to be used very similarly.

    Something like:

     

    ClearCollect(*Collection*, GroupBy(*DataSource*, DatasetNo))

     

     

    To get strictly what you wanted, with columns for each field, as long as you know the names of all fields going in, you could do a forall where you check what the field name is, check if a record with that datasetno exists yet, and if it does patch to that record, otherwise create a new one. Something along the lines of:

     

    ForAll(
     *DataSource* As DS,
     Switch(
     DS.FieldName,
     "Name",
     If(
     CountIf(*Collection*, DatasetNo = DS.DatasetNo) = 0,
     Collect(*Collection*, {DatasetNo: DS.DatasetNo, Name: DS.Value}),
     Patch(*Collection*, LookUp(*Collection*, DatasetNo = DS.DatasetNo), {Name: DS.Value})
     ),
     "Company",
     If(
     CountIf(*Collection*, DatasetNo = DS.DatasetNo) = 0,
     Collect(*Collection*, {DatasetNo: DS.DatasetNo, Company: DS.Value}),
     Patch(*Collection*, LookUp(*Collection*, DatasetNo = DS.DatasetNo), {Company: DS.Value})
     ),
     "Email",
     If(
     CountIf(*Collection*, DatasetNo = DS.DatasetNo) = 0,
     Collect(*Collection*, {DatasetNo: DS.DatasetNo, Email: DS.Value}),
     Patch(*Collection*, LookUp(*Collection*, DatasetNo = DS.DatasetNo), {Email: DS.Value})
     )
     )
    )

     

    This is obviously a lot longer(and maybe there's a nicer way, but this is what I thought of first), but something like it should convert your first table to your second, in the form of a collection.

     

    Hope this helps!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Apps

#1
Kalathiya Profile Picture

Kalathiya 401

#2
WarrenBelz Profile Picture

WarrenBelz 334 Most Valuable Professional

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 325 Super User 2025 Season 2

Last 30 days Overall leaderboard