Convert Columns to Rows

[P_SA]

Hello, I have an excel spreadsheet connected to my PowerApps. Is there a way to convert the columns to rows in PowerApps.

Below is how the data looks in the excel spreadsheet.

The columns in the excel are  Roles, RoleName, checklist1, and so on. 

In my collection I view the data as below in a single row. I want to display this data as a gallery view, but since this is a single row, the data comes under one gallerytemplate. How I can I show each data in different gallery templates vertically one by one

Greatly appreciate any help,. Thanks in advance.

[v-jefferni]

Hi @P_SA ,

If I understand correctly, would you like to split all fields of a single record to a single column table? 

If so, please refer to below thread, try and use the Concat & Split functions to achieve that need:

https://powerusers.microsoft.com/t5/Building-Power-Apps/split-record-into-single-column-table/td-p/1...

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[P_SA]

Hi , Will concat and split also hold true for my data which is dynamic. i,e 
when I filter my source with below  formula , it returns different set of records every time the roleID is different.  I want to dynamically have a single column table for the source filtered using 
filter(datasource, roleID = VarRoleID)

suggestions please

[v-jefferni]

Hi @P_SA ,

There is already a solution in that post.

On your end:

Split(
    Left(
       Concat(Filter(colquestioms,'Role ID'= varRoleID),Column1&", "&Column2&", "Column3&", "&...),
       Len(Concat(Filter(colquestioms,'Role ID'= varRoleID),Column1&", "&Column2&", "Column3&", "&...))-2
    ),
    ", "
)

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[P_SA]

Hi @v-jefferni , thanks for replying. But in my case, the no of columns varies based on the roleID. So my question here is, how do i dynamically split the columns? I hope you understand what I mean. Apologies if I am not able to make you understand

[v-jefferni]

Hi @P_SA ,

Sorry but am still confusion. Why the no of columns varies based on the roleID? You have a table, filter it, then the result table would have the same columns as before, no matter how many rows are there in the result table.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[P_SA]

I am attaching the excel file , Please filter it based on the roleID , You can see that based on roleID the no of  columns are different.  Please let me know if this makes sense.

[v-jefferni]

Hi @P_SA ,

I know your concern now. However, even if no value in a field, the column will exist though. So need no worries about those blank fields, but you could filter the result table to exclude those blank rows:

Filter(Split(
    Left(
       Concat(Filter(colquestioms,'Role ID'= varRoleID),Column1&", "&Column2&", "Column3&", "&...),
       Len(Concat(Filter(colquestioms,'Role ID'= varRoleID),Column1&", "&Column2&", "Column3&", "&...))-2
    ),
    ", "
),
!IsBlank(Result)
)

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[P_SA · Accepted solution]

Thanks , I managed to get it working.  Appreciate your help.

[v-jefferni]

Hi @P_SA ,

Good to hear that you have made it work. 

Could you please consider share your solution with the community?  And if my post helps, then please consider Accept it as the solution to help the other members find it.

Best regards,
Community Support Team _ Jeffer Ni