Unanswered

Filtering errors (excel - collections and so on)

(1) Share

Report

Posted on by WorkingRicardo

818

First of all, I know Excel is the worst I can be using. Trust me, if I'm using it, it's because there's no other alternative. So telling me to use anything other than Excel will not fix my problem.

I have an Excel table of over 8000 items. This has a column named "Salesperson code". In another table I have all those stored and linked with the users email who will use the app.

So I want to show only the rows correspondent to the user who is currently using the app. In theory I know how to use this (MyUser().Email); that's the easy part. I need to filter somehow, lets call it ExcelTable, that matches to that Salesperson Code.

I've been trying to filter first with data I know exist in the table, and that there are at least two items that would match. So for example creating a collection like this:

ClearCollect(
    FilteredClients;
    Filter(ExcelTable; 'Salesperson Code' = "XXXX")
)
 
That creates this error: Error trying to retrieve data from the network: !CKlxd!Syntax error at position 16 in 'Salesperson Code eq 'XXX'. PIOC ! inner exception: !CKlxd!Syntax error at position 16 in 'Salesperson Code eq 'XXXX'. PIOC ! 
 
Tried creating a collection first of the Excel table, but for delegation purposes this will not suffice either.
 
So...What CAN I do :( ?

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Michael E. Gernaey 53,315 Super User 2025 Season 2 on at

Like (0)

Report

Hi @WorkingRicardo

Can you share visually what it looks like, even if you fake the data for a row or 2.

Has the excel file changed at all? I know you said you have a Table around the data, so just checking.

Also, can you try going to your Data Tab

1) Click the excel

2) Select the ...

3) Choose Refresh

if that doesn't work

do 1 and 2 but select Edit Data and see if it can open the file. If it cannot then your code won't work and you should re-attach the file (datasource) to the app.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report
Hi @WorkingRicardo,

I think I was one of the "offenders" stating the obvious about Excel in one of your earlier posts - now I know you have no other option, we need to do "the best we can". I believe the issue here is that Excel cannot parse the field name with a space in it. Two options - the first may not fix it
ClearCollect( FilteredClients; Filter( ExcelTable; [@'Salesperson Code'] = "XXXX" ) )

or rename the column
ClearCollect( FilteredClients; Filter( ExcelTable; SalespersonCode = "XXXX" ) )

The other option is to go down the track of Power Automate using the Excel Online (Business) connector - then a filter query with List Rows Present in a Table to get the data you need.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No