PowerApps search function on Excel Online

(0) Share

Report

Posted on by jbrit2020

638

Hello all,

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.

Error below:

Is this an Excel Online connector limitation? Is there any other way around it?

Regards

Categories:

Building Power Apps

All responses (20)

Answers (2)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@jbrit2020

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.
jbrit2020 638 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@jbrit2020

<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.
jbrit2020 638 on at

Like
a
(0)

Report
Copy link

Link copied!

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?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@jbrit2020

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.
jbrit2020 638 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@jbrit2020

Yep, you can try that as well....hopefully that will work fine for you.
jbrit2020 638 on at

Like
a
(0)

Report
Copy link

Link copied!

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?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@jbrit2020

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.
jbrit2020 638 on at

Like
a
(0)

Report
Copy link

Link copied!

No @RandyHayes sorry my post was probably not clear enough. I tried on different tenants and none worked. Different machines, different accounts, different companies. The result is the same, it fails with the same message.