PowerApps - Encountering issues with filtering data from your Excel worksheet

If you are encountering issues with filtering data from your Excel worksheet in PowerApps. 

1. Excel Business Connector Limitation:

   • If you’re using the Excel business connector, there’s a limitation: you can only filter by one column. This is why you’re seeing the error message about only one “eq, ne, … operation” being allowed.
   • To work around this, nest your filter using the With function. Here’s an example:

     With({
         filterOne: Filter(TBL_Ondernemingsplan, Field = Drpdown1.Selected.Result)
     },
     Filter(filterOne, Field2 = Drpdown2.Selected.Result)
     )
     

   • Replace TBL_Ondernemingsplan, Field, Drpdown1, and Drpdown2 with your actual table name, field names, and control names.

2. Excel Column Names with Spaces:

   • If your column names include spaces, PowerApps might not handle them correctly. You can use the RenameColumns function to remove spaces from column names:

     Filter(RenameColumns(Employee, "First name", "Firstname"), Firstname = "Tim")
     

3. Check Data Source:

   • Ensure that your app is actually getting data from the Excel connector. Sometimes, if it’s “stuck,” even fixing the formula won’t immediately reflect the changes. Save and exit the designer to refresh the connection.

4. Consider Using Collections:

   • Collect the Excel data into a collection within your app. Then use the collection as the data source for filtering. This can help avoid limitations related to direct Excel connections.

5. Move to SharePoint:

   • If your app becomes more complex, consider moving to SharePoint as a data source. SharePoint handles concurrent access better than Excel.

Remember to adapt these solutions to your specific scenario.