How to transpose a sharepoint list in a data table in powerapps

[rupa1]

Hello,

I have a single row where i have all 11 -12 team members names in different columns - now i want to access this sharepoint list in a data table in powerapps but i want to have all these team members names in a single column -how to achievethis?

[RandyHayes · Accepted solution]

@rupa1 

One possibility is to combine the columns into a semicolon separated string and then use the Split function to Split them on the semicolons into a Collection.  Then use the collection as the source for your DataTable.

[v-yutliu-msft]

Hi @rupa1 ,

Do you want to display multiple columns data in one column and display data in a data table?

Could you tell me:

1)those fields' data type?

2)if those fields are person type, whether those allow multiple value?

I suggest you use AddColumns function to add a new column with multiple fields' data.

1)If those fields are text type, you could set the data table's Items like this:

AddColumns(tablename,"newcolumn",field1&","&field2&","&field3&","&field4....)

2)If those fields are person type and not allow multiple value, you could set the data table's Items like this:

AddColumns(tablename,"newcolumn",field1.DisplayName&","&field2.DisplayName&","&field3.DisplayName&","&field4.DisplayName....)

3)If those fields are person type and  allow multiple value, you could set the data table's Items like this:

AddColumns(tablename,"newcolumn",Concat(field1,DisplayName&",")&","&Concat(field2,DisplayName&",")&","&Concat(field3,DisplayName&",")&","&Concat(field4,DisplayName&",")...
)

Then the new added column will contain multiple columns data.

Best regards,

[rupa1]

Thank you for the help but my requirement is to transpose the table from row to column it and not add column with comma separated names. Example - I have two rows with 11 columns( team member names) - I need to have 11 rows with 1 column as team member.

[RandyHayes]

@rupa1 

So, as I mentioned in my previous response, you can consider to combine the columns and then Split them into a table.

Ex.  You have columns called Name1, Name2, Name3, Name4 (yes, I know you have more, but to be brief and sight an example, we'll go with just 4)

ClearCollect(
    NameCollection,
    Split(yourRecord.Name1 & ";" & yourRecord.Name2 & ";" & yourRecord.Name3 & ";" & yourRecord.Name4, ";")
)

This will give you a Table called NameCollection that will have one column called Result that will have 4 rows in it.

If you'd like to rename the column to something else more meaningful (like TeamMember), you can do this:

ClearCollect(
    NameCollection,
    RenameColumns(
        Split(yourRecord.Name1 & ";" & yourRecord.Name2 & ";" & yourRecord.Name3 & ";" & yourRecord.Name4, ";"),
        "Result", "TeamMember"
    )
)

[rupa1]

hello I am getting stuck in split option

1. I have created a concatenated column by using the ADDCOLUMN function- the column name as Team_member1_Column1( all names are now concatenated separated by comma (,)) 

2. when I am trying to create a collection only storing the contents in the Team_member1_Column1- this is not working 😞

ClearCollect(collection_name,Team_member1_Column1.FieldDisplayName,",")- please help as I am very new to powerapps

[rupa1]

hi, 

i wanted to implement the same logic but i am failing to get the record name - i am storing the data in a concatenated column as Team_member1_Column1 and when i am trying to pull the contents of this column in a collection -its not working. please help.

[rupa1]

yes ..it finally worked