PowerApps: DataTable Show/Hide Columns Dynamically

Hello Friends,

Welcome back with another post on PowerApps. Today, we will learn about show/hide of column in PowerApps DataTable 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. Create a list in SharePoint.
2. List Name: EmployeeInfo
3. Columns-
1. Title: Single line of text
2. First Name: Single line of text
3. Last Name: Single line of text
4. Date Of Joining: Date and Time
5. Salary: Number
6. Designation: Choice (Software Engineer | Module Lead | Team Lead | Project Lead | Project Manager | Program Manager)
7. Experience: Choice (Fresher | Intermediate | Experienced | Proficient)
4. 
5. Insert some data in this list.
6. 
7. Now, open the PowerApps maker portal and create a blank canvas app (Table format).
8. 
9. Save the app so that whatever changes you will make onwards, will get auto saved.
10. 
11. Rename the screen from Screen1 to ScreenHome.
12. 
13. Start, adding controls-
14. For Views list:
1. Label-
1. Name: Label_SelectView
2. Text: View:
2. Drop down-
1. Name: Dropdown_SelectView
15. 
16. For Columns list:
1. Label-
1. Name: Label_SelectColumns
2. Text: Column(s):
2. Combo box-
1. Name: Combobox_SelectColumns
17. 
18. For Free Text Search:
1. Label-
1. Name: Label_Search
2. Text: Column(s):
2. Text input-
1. Name: TextInput_Search
2. Default: ""
3. Tooltip: Search Box
19. 
20. For Additional Column Base Search:
1. Radio-
1. Name: Radio_SelectExperience
21. 
22. Now, we will add Reset icon. The purpose of this icon is to Reset the screen controls as well as all the collections.
1. Icon "Reset"-
1. Name: Icon_ResetForm
2. Tooltip: Reset Form
3. Visible: false
23. 
24. Add one more Reset icon. This icon will be used to reset the below controls upon change of view.
1. Combobox_SelectColumns
2. TextInput_Search
3. Radio_SelectExperience
25. 
26. Don't get confused with the icon. The same type of icon is used for two different activities.
27. The only control left to add in DataTable. We will add DataTable at last. Before that, we will add our code upon all the existing controls. Start from the ScreenHome.
28. Control Name: ScreenHome
29. Property: OnVisible
30. Function:

1. Select(Icon_ResetForm);
   

31. 
32. Control Name: Icon_ResetForm
33. Property: OnSelect
34. Function:

1. // **** All Columns With Order ****
   ClearCollect(
       collAllColumnsWithOrder,
       {
           ColumnName: "ID",
           OrderNo: 1
       },
       {
           ColumnName: "Title",
           OrderNo: 2
       },
       {
           ColumnName: "First Name",
           OrderNo: 3
       },
       {
           ColumnName: "Last Name",
           OrderNo: 4
       },
       {
           ColumnName: "Date Of Joining",
           OrderNo: 5
       },
       {
           ColumnName: "Salary",
           OrderNo: 6
       },
       {
           ColumnName: "Designation",
           OrderNo: 7
       },
       {
           ColumnName: "Experience",
           OrderNo: 8
       }
   );
   // **** All Expereinces Plus ALL ****
   ClearCollect(
       collExperiences,
       {Experience: "All"},
       {Experience: "Fresher"},
       {Experience: "Intermediate"},
       {Experience: "Experienced"},
       {Experience: "Proficient"}
   );
   // **** All Views With Sequence And Default Columns ****
   ClearCollect(
       collViewsWithDefaultColumnsOrder,
       {
           SeqNo: 1,
           ViewName: "All Employees",
           DefaultColumnsWithOrder: Table(
               {ColumnName: "Title"},
               {ColumnName: "First Name"},
               {ColumnName: "Last Name"},
               {ColumnName: "Date Of Joining"},
               {ColumnName: "Salary"},
               {ColumnName: "Designation"},
               {ColumnName: "Experience"}
           )
       },
       {
           SeqNo: 2,
           ViewName: "Software Engineer",
           DefaultColumnsWithOrder: Table(
               {ColumnName: "Title"},
               {ColumnName: "First Name"},
               {ColumnName: "Last Name"},
               {ColumnName: "Date Of Joining"},
               {ColumnName: "Experience"}
           )
       },
       {
           SeqNo: 3,
           ViewName: "Module Lead",
           DefaultColumnsWithOrder: Table(
               {ColumnName: "Title"},
               {ColumnName: "First Name"},
               {ColumnName: "Last Name"},
               {ColumnName: "Designation"},
               {ColumnName: "Experience"}
           )
       },
       {
           SeqNo: 4,
           ViewName: "Project Manager",
           DefaultColumnsWithOrder: Table(
               {ColumnName: "Title"},
               {ColumnName: "First Name"},
               {ColumnName: "Last Name"},
               {ColumnName: "Date Of Joining"}
           )
       },
       {
           SeqNo: 5,
           ViewName: "Program Manager",
           DefaultColumnsWithOrder: Table(
               {ColumnName: "Title"},
               {ColumnName: "First Name"},
               {ColumnName: "Last Name"},
               {ColumnName: "Designation"},
               {ColumnName: "Experience"}
           )
       }
   );
   // **** All Default Collection With Order ****
   ClearCollect(
       collDefaultColumnsWithOrder,
       Filter(
           collAllColumnsWithOrder,
           ColumnName in First(
               Sort(
                   collViewsWithDefaultColumnsOrder,
                   SeqNo,
                   Ascending
               )
           ).DefaultColumnsWithOrder.ColumnName
       )
   );
   // **** Current Columns Collection To Display (Customized By User) ****
   ClearCollect(
       collCurrentDisplayColumnsWithOrder,
       collDefaultColumnsWithOrder
   );
   
   Reset(Dropdown_SelectView);
   Select(Icon_ResetControls);
   

35. This code needs to be explained. Here, we are creating below collections-
1. collAllColumnsWithOrder- This collection will hold all the column names that we created in list (you may include Created, Created By, Modified, Modified By as well) alongwith their order in which they will be displayed in DataTable. 
1. Use: This collection will be used to bind with the Items property of "ComboBox_SelectColumns" control.
2. collExperiences- This collection will hold all the choices we have provided in Experience column of the list alongwith one more choice "All" as the primary one.
1. Use: This collection will be used to bind with Items property of "Radio_SelectExperience" control.
3. collViewsWithDefaultColumnsOrder- This collection will hold the name of Views that will be available to user to select alongwith the columns that need to be displayed in DataTable by default upon selection of that particular view. Ordering of columns will be decided by the collection "collAllColumnsWithOrder".
1. Use: This view will be used to bind the Items property of "Dropdown_SelectView" control.
4. collDefaultColumnsWithOrder- This collection will hold the default columns as well as their order based upon the view selected in co-ordination with "collAllColumnsWithOrder".
1. Use: This collection will be used to set the DefaultSelectedColumns property of "ComboBox_SelectColumns" control.
5. collCurrentDisplayColumnsWithOrder- This collection will be used to Show/Hide the columns in DataTable.
36. 
37. Control Name: Icon_ResetControls
38. Property: OnSelect
39. Function:

1. Reset(ComboBox_SelectColumns);
   Reset(TextInput_Search);
   Reset(Radio_SelectExperience);

40. 
41. Now, Save the app and the play. Then click on Icon_ResetForm. It will create all the required collections. Stop the play mode and return to design mode. Now, update the properties of below controls-
42. Control Name: Dropdown_SelectView
1. Property: Items
2. Function:

1. Sort( collViewsWithDefaultColumnsOrder,SeqNo,Ascending).ViewName
   

3. Property: OnChange
4. Function:

1. Select(Icon_ResetControls);
   ClearCollect(
       collDefaultColumnsWithOrder,
       Filter(
           collAllColumnsWithOrder,
           ColumnName in First(
               Filter(
                   collViewsWithDefaultColumnsOrder,
                   ViewName = Dropdown_SelectView.SelectedText.ViewName
               )
           ).DefaultColumnsWithOrder.ColumnName
       )
   );
   ClearCollect(
       collCurrentDisplayColumnsWithOrder,
       collDefaultColumnsWithOrder
   )
   

5. 
   
43. Control Name: ComboBox_SelectColumns 
1. Property: Items 
2. Function:

1. collAllColumnsWithOrder

3. Property: OnChange
4. Function: 

1. ClearCollect(
       collCurrentDisplayColumnsWithOrder,
       Filter(
           collAllColumnsWithOrder,
           ColumnName in ComboBox_SelectColumns.SelectedItems.ColumnName
       )
   );
   

5. Property: DefaultSelectedItems
6. Function: 

1. collDefaultColumnsWithOrder.ColumnName
   

44. Control Name: Radio_SelectExperience
1. Property: Items
2. Function:

1. collExperiences
   

3. Property: Default
4. Function:

1. "All"
   

45. Save the App.
46. Now, we will add DataTable. But before that, we will add the SharePoint list as DataSource. For this, click on DataSource icon from left navigation and then click on "Add Data".
47. 
48. Search for SharePoint.
49. It will show you the available connections for the SharePoint otherwise it will ask to connect.
50. 
51. Select the available connection. It will show the list of sites at right side.
52. Select the appropriate site. It will show then show the available lists for that site.
53. Select the list we created at the beginning of this post.
54. and click on Connect.
55. 
56. Now, add DataTable.
1. Name: DataTable_Result
57. Update the properties as below-
1. Property: Items
2. Function:

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

58. Let's first talk about this code. Here, we have not bind the Items property directly to datasource. Instead, we are filtering the data based upon the below controls values and the binding it to the Items property-
1. View selected from Dropdown_SelectView control (then applying filter upon Designation column, as we had made views based upon designation)
2. Experience selected from Radio_SelectExperience control (then applying filter upon Experience column)
3. Filter text entered by user in TextInput_Search control (then applying filter on different columns of list)
59. 
60. This is how, data get bind with DataTable. Now save the app and then play. click on Icon_ResetForm. Close the play mode of app and check if the columns start displaying in DataTable. If not, then click on "Edit Fields". It will open a new popup "Data". Here you will find the columns, otherwise click on "Add field" to add the fields. 
61. 
62. Now the MOST IMPORTANT part of the post - Show / Hide of columns.
63. For this, click on first column from the left controls tree-
64. 
65. Now, update below properties-
1. Property: Order
2. Function:
1.  First(

       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(ColumnName,"_x0020_"," ") = "ID"
       )
   ).OrderNo
   

3. Property: Visible
4. Function:

1. CountRows(
       Filter(
           collCurrentDisplayColumnsWithOrder,
           Substitute(ColumnName,"_x0020_"," ") = "ID"
       ) 

   ) > 0 
66. Here "ID" is the name of column as we are updating the properties of ID column.
67. Similarly, for each control, update the "Order" & "Visible" properties. Everytime, you update the property, replace the "ID" with the name of that column.
68. Save the app and it's DONE. Play the app
69. 
70. Change the view. I changed to "Software Engineer".
71. 
72. Unselect the column "Date Of Joining" from the combo box.
73. 
74. Select the "Fresher" from Experience radio box.
75. 
76. Search for "man" from Free Text Search box.
77. 
78. click on Reset Form icon.
79. 
80. Change the view to "Module Lead".
81. 
82. Keep playing.
83. This is how, you can implement the Show / Hide feature in Data Table. You can implement the same in Gallery as well. The only additional part is that instead of setting the Visible property of columns, there you have to set the-
1. Visible property of each nested control as we use labels/buttons.. in gallery to show the data.
2. Width property to 0 when control needs to Hide and back to original width when control needs to Show. The reason behind is whether control is visible / hidden, it holds it's space in gallery. therefore, in order to bring the next control adjacent to previous one, we need to reduce the width of hidden control to 0.
3. There will be no Order property. Therefore, you need to place the controls according to the order, you want to display.
84. Please find below control names, used in the app. In case of any typo mistake in this post, please make it correct using below screenshot.
85. 
86. You may achieve the same functionality using Gallery as well.
1. PowerApps: Gallery Show/Hide Columns Dynamically
   
87. 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 !