Answered

Transposing table from single rows to columns

(0) Share

Report

Posted on by skoerber

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:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

skoerber 20 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

madlad 2,637 Moderator on at

Like (1)

Report
Copy link

Link copied!
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!

Was this reply helpful? Yes No