For the first time since starting to use powerapps I will have to create an app using Excel Online and I'm facing the first issues.
I can't seem to Search/Filter/Sort or anything similar with my Excel table.
Here's the piece of code I applied on the Gallery: Search(Table2,TextInput1.Text,"Closed Date","Accept into Risk Register")
The exact same formula but in the SQL table works perfectly.
Is this an Excel Online connector limitation? Is there any other way around it?
Solved! Go to Solution.
Okay then, what you are doing works perfectly, so there must be something initially going on.
You mention Table2 in your original formula - is Table2 from your Excel file? How are you getting that connected to your data?
Normally, you would be using the OneDrive connector to connect to your Excel file. At that point, when you add the connector, it will ask for the Excel file and the table. Once you select, those tables are available in your app.
My assumption so far has been that this was the process you used.
Use the OneDrive for Business connector instead.
Please consider changing your Formula to the following:
Search(Table2,TextInput1.Text,'Closed Date', 'Accept into Risk Register')
Note: Columns that have spaces in them should be surrounded by SINGLE quotes, not double quotes like you had.
I hope this is helpful for you.
Thanks for replying. Still doesn't work. The double quotes is the automatic format that powerapps suggests me to use when I start typing. When I use your recommendation it says there are invalid arguments.
<Palm plant to head.> Sorry - Totally missed the Search. Yes, Search will need its fields double quoted.
Is your Date column formatted as a date? Or in other words, does PowerApps see it as a date? If so, you will not be able to use that in a Search function. Search will only search text columns.
It is formatted as date, but I just tried removing that column and using a normal text column instead of date, and the result is still the same. could it be because im using Excel Online as the connector?
If you are actually getting data from the excel connector, then there is no issue with that.
However, if you did get this error as you displayed, it is most likely "stuck". Meaning that you can fix your formula, but it will still show.
First try to fix your formula to only search text columns and then save and exit out of the designer. Then go back in and see if the error is cleared.
ok, to make sure I will create a brand new canvas app from scratch and connect to the file and recreate the formula just for text columns.
Yep, you can try that as well....hopefully that will work fine for you.
Ok, so here's what I've tried:
1 - Remove connector and reconnect and create search formula with a single Text column.
2 - Create new canvas app from scratch create connection to excel file and create search formula with a single Text column.
3 - Create a new table with just two columns with dummy data in a new excel file, create new canvas app, connect to data and create search formula with a single Text column.
4 - Try on a different laptop connected to a different business enterprise DCN space altogether (different companies) , and repeated all of the previous tests. The result is always the same, "The request operation is invalid. Server Response: Table1 failed: ')' or ',' expected at position 16 in 'contains....."
I'm going to guess this really is a limitation of a Excel Online connection? or perhaps a problem that has gone unnoticed?
So it works in your own tenant then. Your problem is going across tenant. I am sure then it is completely based on the security and permissions across the two.
Check out new user group experience and if you are a leader please create your group
Did you miss the call?? Check out the Power Apps Community Call here!
See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.
ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.