PowerApps: Gallery Show/Hide Columns Dynamically

Hello Friends,

Welcome back with another post on PowerApps. In our last post, we learned about the Show/Hide of columns in DataTable. Here is the link of the post.

1. PowerApps: DataTable Show/Hide Columns Dynamically

If we wish to have the same feature using Gallery, then would it be possible? Because, in Gallery, there are no columns? The answer is YES. We can achieve the same functionality using Gallery as well. Today, we will learn about show/hide of column in PowerApps Gallery dynamically. The objective is to create an App where user can-

1. Select the columns to display as per it's choice
2. Select any of the available view and the columns should display based upon that view. Also, the data must get filter accordingly
3. Apply additional filter based upon a particular column
4. Apply additional filter using a Free Text Search

This is what, we are going to build-

Let's start-

1. We already have the screen designed last time in our last post (see link above). We are going to use the same screen.
2. 
3. Click on ellipses (3 dots) ahead to "ScreenHome" and select "Duplicate Screen".
4. 
5. It will create a duplicate screen with name as "ScreenHome_1" including all the controls with suffixed as "_1".
6. 
7. Now, rename the screen from "ScreenHome_1" to "ScreenGallery". Renaming is also simple. 
8. Click on ellipses (3 dots) ahead to "ScreenHome_1" and select "Rename".
9. 
10. Now, we can see that DataTable_Result is also got renamed as "DataTable_Result_1". Copy the functions written on below properties for DataTable_Result_1.
1. Height
2. Items
3. Width
4. X
5. Y
6. Visible property function for any one column.
11. Now, remove the DataTable_Result_1 control. 
12. From here, we will first add "Text label" that will serve the purpose of Gallery Headers. Then we will add a Gallery (Blank vertical gallery). Later, we will add Labels inside gallery template that will serve the purpose to display the items data.
13. Let's resume the process.
14. Add a Text label and update the properties as below-
1. Name: Label_Header_ID
2. Text: ID
3. BorderColor: RGBA(255, 255, 255, 1)
4. BorderThickness: 1
5. Color: RGBA(255, 255, 255, 1)
6. Fill: RGBA(0, 18, 107, 1)
7. Height: 40
8. X: Label_SelectView_1.X
9. Y: Label_SelectColumns_1.Y+Label_SelectColumns_1.Height+50
10. Visible & Width properties will be updated later on.
11. Lastly, from the right side properties pane, set "Text Alignment" to "Align center"
15. 
16. Add another Text label and update the properties as below. (Better, make a duplicate copy of the "ID" label so that you have to set only 4 properties rather than these many-
1. Name: Label_Header_Title
2. Text: Title
3. X: Label_Header_ID.X+Label_Header_ID.Width
4. Y: Label_Header_ID.Y
5. Visible & Width properties will be updated later on
17. 
18. Here, we have changed the way to define the X & Y properties. Now, onwards, only Name, Text & X properties will be updated. Every next label will have X property a the sum of X & Width property of previous label. Rest will remain common.
19. Make a duplicate copy of this "Title" label and update properties as below-
1. Name: Label_Header_First_Name
2. Text: First Name
3. X: Label_Header_Title.X+Label_Header_Title.Width
4. Visible & Width properties will be updated later on
20. Make a duplicate copy of this "First Name" label and update properties as below-
1. Name: Label_Header_Last_Name
2. Text: Last Name
3. X: Label_Header_First_Name.X+Label_Header_First_Name.Width
4. Visible & Width properties will be updated later on
21. Make a duplicate copy of this "Last Name" label and update properties as below-
1. Name: Label_Header_Date_Of_Joining
2. Text: Date Of Joining
3. X: Label_Header_Last_Name.X+Label_Header_Last_Name.Width
4. Visible & Width properties will be updated later on
22. Make a duplicate copy of this "Date Of Joining" label and update properties as below-
1. Name: Label_Header_Salary
2. Text: Salary
3. X: Label_Header_Date_Of_Joining.X+Label_Header_Date_Of_Joining.Width
4. Visible & Width properties will be updated later on
23. Make a duplicate copy of this "Salary" label and update properties as below-
1. Name: Label_Header_Designation
2. Text: Designation
3. X: Label_Header_Salary.X+Label_Header_Salary.Width
4. Visible & Width properties will be updated later on
24. Make a duplicate copy of this "Designation" label and update properties as below-
1. Name: Label_Header_Experience
2. Text: Experience
3. X: Label_Header_Designation.X+Label_Header_Designation.Width
4. Visible & Width properties will be updated later on
25. Finally, the outcome will be-
26. 
27. Now, we will add a "Blank vertical gallery" and update it's properties as below-
1. Name: BVG_Gallery_Result
2. X: Label_SelectView_1.X
3. Y: Label_Header_ID.Y+Label_Header_ID.Height
4. Width: ScreenGallery.Width
5. Height: ScreenGallery.Height-BVG_Gallery_Result.Y
6. TemplateSize: 50
7. TemplatePadding: 0
8. BorderThickness: 1
28. Now, we will update the "Items" property of this gallery. We have already copied the function for Items property from the DataTable (we deleted in beginning). The same function, we need to paste it over here.

1. Switch(
       Dropdown_SelectView_1.SelectedText.ViewName,
       "All Employees",
       Filter(
           EmployeeInfo,
           Experience.Value in If(
               Radio_SelectExperience_1.SelectedText.Experience = "All",
               Experience.Value,
               Radio_SelectExperience_1.SelectedText.Experience
           ) && (Trim(TextInput_Search_1.Text) in 'First Name' || Trim(TextInput_Search_1.Text) in 'Last Name' || 
   Trim(TextInput_Search_1.Text) in Designation.Value || Trim(TextInput_Search_1.Text) in Experience.Value || 
   Upper(Title) = Upper(Trim(TextInput_Search_1.Text))) || Text(
               'Date Of Joining',
               "m/d/yyyy",
               "en-US"
           ) = Text(
               Trim(TextInput_Search_1.Text),
               "m/d/yyyy",
               "en-US"
           ) || Text(Trim(Salary)) = Trim(TextInput_Search_1.Text)
       ),
       "Software Engineer",
       Filter(
           EmployeeInfo,
           "Software Engineer" in Designation.Value,
           Experience.Value in If(
               Radio_SelectExperience_1.SelectedText.Experience = "All",
               Experience.Value,
               Radio_SelectExperience_1.SelectedText.Experience
           ) && (Trim(TextInput_Search_1.Text) in 'First Name' || Trim(TextInput_Search_1.Text) in 'Last Name' || 
   Trim(TextInput_Search_1.Text) in Designation.Value || Trim(TextInput_Search_1.Text) in Experience.Value || 
   Upper(Title) = Upper(Trim(TextInput_Search_1.Text))) || Text(
               'Date Of Joining',
               "m/d/yyyy",
               "en-US"
           ) = Text(
               Trim(TextInput_Search_1.Text),
               "m/d/yyyy",
               "en-US"
           ) || Text(Trim(Salary)) = Trim(TextInput_Search_1.Text)
       ),
       "Module Lead",
       Filter(
           EmployeeInfo,
           "Module Lead" in Designation.Value,
           Experience.Value in If(
               Radio_SelectExperience_1.SelectedText.Experience = "All",
               Experience.Value,
               Radio_SelectExperience_1.SelectedText.Experience
           ) && (Trim(TextInput_Search_1.Text) in 'First Name' || Trim(TextInput_Search_1.Text) in 'Last Name' || 
   Trim(TextInput_Search_1.Text) in Designation.Value || Trim(TextInput_Search_1.Text) in Experience.Value || 
   Upper(Title) = Upper(Trim(TextInput_Search_1.Text))) || Text(
               'Date Of Joining',
               "m/d/yyyy",
               "en-US"
           ) = Text(
               Trim(TextInput_Search_1.Text),
               "m/d/yyyy",
               "en-US"
           ) || Text(Trim(Salary)) = Trim(TextInput_Search_1.Text)
       ),
       "Project Manager",
       Filter(
           EmployeeInfo,
           "Project Manager" in Designation.Value,
           Experience.Value in If(
               Radio_SelectExperience_1.SelectedText.Experience = "All",
               Experience.Value,
               Radio_SelectExperience_1.SelectedText.Experience
           ) && (Trim(TextInput_Search_1.Text) in 'First Name' || Trim(TextInput_Search_1.Text) in 'Last Name' || 
   Trim(TextInput_Search_1.Text) in Designation.Value || Trim(TextInput_Search_1.Text) in Experience.Value || 
   Upper(Title) = Upper(Trim(TextInput_Search_1.Text))) || Text(
               'Date Of Joining',
               "m/d/yyyy",
               "en-US"
           ) = Text(
               Trim(TextInput_Search_1.Text),
               "m/d/yyyy",
               "en-US"
           ) || Text(Trim(Salary)) = Trim(TextInput_Search_1.Text)
       ),
       "Program Manager",
       Filter(
           EmployeeInfo,
           "Program Manager" in Designation.Value,
           Experience.Value in If(
               Radio_SelectExperience_1.SelectedText.Experience = "All",
               Experience.Value,
               Radio_SelectExperience_1.SelectedText.Experience
           ) && (Trim(TextInput_Search_1.Text) in 'First Name' || Trim(TextInput_Search_1.Text) in 'Last Name' || 
   Trim(TextInput_Search_1.Text) in Designation.Value || Trim(TextInput_Search_1.Text) in Experience.Value || 
   Upper(Title) = Upper(Trim(TextInput_Search_1.Text))) || Text(
               'Date Of Joining',
               "m/d/yyyy",
               "en-US"
           ) = Text(
               Trim(TextInput_Search_1.Text),
               "m/d/yyyy",
               "en-US"
           ) || Text(Trim(Salary)) = Trim(TextInput_Search_1.Text)
       ) 

   ) 
29. 
30. Now, we will add Text labels to show the items data. Click on "Edit gallery" icon shown in gallery.
31. 
32. It will change the template to edit mode.
33. 
34. Now add "Text label" and update properties as below-
1. Name: Label_Value_ID
2. Text: ThisItem.ID
3. BorderThickness: 1
4. Height: BVG_Gallery_Result.TemplateHeight
5. X: Label_Header_ID.X
6. Y: 0
7. Visible: Label_Header_ID.Visible
8. Width: Label_Header_ID.Width
35. Add "Text label" and update properties as below-
1. Name: Label_Value_Title
2. Text: ThisItem.Title
3. BorderThickness: Label_Value_ID.BorderThickness
4. Height: BVG_Gallery_Result.TemplateHeight
5. X: Label_Header_Title.X
6. Y: Label_Value_ID.Y
7. Visible: Label_Header_Title.Visible
8. Width: Label_Header_Title.Width
36. Make a duplicate copy of "Title" value label and update properties as below-
1. Name: Label_Value_First_Name
2. Text: ThisItem.'First Name'
3. X: Label_Header_First_Name.X
4. Visible: Label_Header_First_Name.Visible
5. Width: Label_Header_First_Name.Width
37. Make a duplicate copy of "First Name" value label and update properties as below-
1. Name: Label_Value_Last_Name
2. Text: ThisItem.'Last Name'
3. X: Label_Header_Last_Name.X
4. Visible: Label_Header_Last_Name.Visible
5. Width: Label_Header_Last_Name.Width
38. Make a duplicate copy of "Last Name" value label and update properties as below-
1. Name: Label_Value_Date_Of_Joining
2. Text: ThisItem.'Date Of Joining'
3. X: Label_Header_Date_Of_Joining.X
4. Align: Align.Right
5. Visible: Label_Header_Date_Of_Joining.Visible
6. Width: Label_Header_Date_Of_Joining.Width
39. Make a duplicate copy of "Date Of Joining" value label and update properties as below-
1. Name: Label_Value_Salary
2. Text: Text(ThisItem.Salary,"#00,000.00")
3. X: Label_Header_Salary.X
4. Align: Align.Right
5. Visible: Label_Header_Salary.Visible
6. Width: Label_Header_Salary.Width
40. Make a duplicate copy of "Salary" value label and update properties as below-
1. Name: Label_Value_Designation
2. Text: ThisItem.Designation.Value
3. X: Label_Header_Designation.X
4. Align: Align.Left
5. Visible: Label_Header_Designation.Visible
6. Width: Label_Header_Designation.Width
41. Make a duplicate copy of "Designation" value label and update properties as below-
1. Name: Label_Value_Experience
2. Text: ThisItem.Experience.Value
3. X: Label_Header_Experience.X
4. Align: Align.Left
5. Visible: Label_Header_Experience.Visible
6. Width: Label_Header_Experience.Width
42. The outcome is here.
43. 
44. Now, we will update the Visible & Width properties of Header Labels as below.
45. Update the Width & Visible property of each header as below-
46. Control: Label_Header_ID
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "ID"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "ID"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   ) 

47. Control: Label_Header_Title
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "Title"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "Title"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   ) 

48. Control: Label_Header_First_Name
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "First Name"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "First Name"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   )
   

49. Control: Label_Header_Last_Name
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "Last Name"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "Last Name"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   )
   

50. Control: Label_Header_Date_Of_Joining
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "Date Of Joining"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "Date Of Joining"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   )
   

51. Control: Label_Header_Salary
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "Salary"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "Salary"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   )
   

52. Control: Label_Header_Designation
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "Designation"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "Designation"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   )
   

53. Control: Label_Header_Experience
1. Visible: CountRows(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(
               ColumnName,
               "_x0020_",
               " "
           ) = "Experience"
       )
   ) > 0
   

2. Width: If(

       CountRows(
           Filter(
               collCurrentDisplayColumnsWithOrder,
               Substitute(
                   ColumnName,
                   "_x0020_",
                   " "
               ) = "Experience"
           )
       ) > 0,
       BVG_Gallery_Result.Width / CountRows(collCurrentDisplayColumnsWithOrder),
       0
   )
   

54. 
55. That's all. Now, you can save the app, publish it and play it. Before that, I have to add one button on previous screen (ScreenHome) so that I can navigate to the Gallery screen.
56. Here we go. Start playing the app.
57. Default load
1. 
58. Removed "Date Of Joining" column
1. 
59. Filter on Experience
1. 
60. Free Text Search
1. 
61. Changed View
1. 
62. Selected All Columns
1. 
63. This way, you can achieve the Show/Hide feature using Gallery as well.
64. For me, the number of columns were less, therefore, I am able to manage using Screen Width / No of Columns. In case, the number of columns are more, change the logic of Width accordingly.
65. Next, you may add Export To Excel feature as well.
1. Power Automate: Export To Excel For Dynamic View/Filter Selection In PowerApps

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !