Transposing table from single rows to columns

[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?

[madlad · Accepted solution]

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!

[skoerber]

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.