Skip to main content

Notifications

Power Apps - Building 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?

  • skoerber Profile Picture
    skoerber 20 on at
    Re: Transposing table from single rows to columns

    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
    madlad 2,637 on at
    Re: Transposing table from single rows to columns

    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

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard