cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
P_SA
Helper V
Helper V

Convert Columns to Rows

 

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.

P_SA_0-1620286286436.png

 

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

P_SA_1-1620286409839.png

 

Greatly appreciate any help,. Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

9 REPLIES 9
v-jefferni
Community Support
Community Support

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.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

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


P_SA_0-1620295843721.png

 

 

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.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

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

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.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

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.

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.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

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

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

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,570)