Skip to main content

Notifications

Power Apps - Power Query
Suggested answer

Filter from a sharepoint list returning blank on occasion, despite the conditions matching

Like (0) ShareShare
ReportReport
Posted on 14 Jan 2025 18:05:28 by 4
For context, I'm working on an action tracking app. Separate screens for the current user's actions, grouped actions of different teams etc with all actions stored as individual records in a sharepoint list.
 
My issue is that with certain values in my filter, the returned collection is empty even though it should be populated, and I cannot figure it what's causing this. 
 
ClearCollect(Committee_col, Filter('Actions List', Project_or_committee_name = Selected_committee))
 
As you can see, I am not performing any sort of complex filtering, quite literally just checking if the currently selected project/committee (selected_committee),which is set with a button press, is the same as the value stored in the Project_or_committee_name column. This logic has worked perfectly fine for the last month but isn't now. 
 
For example, some of the committee names include [Tech, Finance, BAU Acquisitions, Swiss Watch] and many more. If I set Selected_committee to "Tech", all relevant records are returned. However if Selected_committee is set to "BAU Acquisitions", my collection is completely empty even though there are records with "BAU Acquisitions" in my sharepoint list. 
 
I've checked, and triple checked all spellings, checked the values being passed to Selected_committee and yet it still only seems to break for a few specific values and work for others but I cannot see any relation between which do and do not work. 
 
I've tried referencing the broken values directly in the filter, copying the exact value from the sharepoint list rather than using Selected_committee, didn't make a difference.
 
Tried changing the filter to a lookUp to see if it'd return a single value but even that comes back null.
 
My sharepoint list is at ~800 rows so I thought maybe it'd be an issue with my sharepoint list getting too big, but even with the whole or part of sharepoint list (tested separately) stored locally in a collection I'm still getting a null return from the filter.  I know the delegation limit is 500 by default, but I had already changed it to 2000 a while ago. I don't see why that would make any difference, especially since it still didn't work with a local copy of the collection.
 
Tried in another, completely blank app with the same sharepoint list connected to see if it was being interfered with somewhere else in the app and still got the same result.
 
Is there something small and obvious that im missing? Cannot for the life of me see why such a simple filter request is causing such an issue so any help would be appreciated.
Categories:
  • Suggested answer
    LP-14011646-0 Profile Picture
    LP-14011646-0 4 on 15 Jan 2025 at 09:31:08
    Filter from a sharepoint list returning blank on occasion, despite the conditions matching
    Issue has been fixed, someone had entered new line characters at the end of some committee names in the sharepoint list which were only visible in the "Edit in grid view" on sharepoint, and not when editing the individual records.
  • LP-14011646-0 Profile Picture
    LP-14011646-0 4 on 15 Jan 2025 at 08:21:17
    Filter from a sharepoint list returning blank on occasion, despite the conditions matching
     
    its a variable, there's no quotations in my actual code on Powerapps though. They're just left over after coping the line over when I had a specific value in there and forgot to remove them when I changed it to Selected_committee on here. I'll edit my original question to prevent further confusion.
     
    I had the same thought in regards to the values with spaces initially, but there are a few that work that have spaces and a few that don't. 
  • WarrenBelz Profile Picture
    WarrenBelz 144,654 on 14 Jan 2025 at 21:37:58
    Filter from a sharepoint list returning blank on occasion, despite the conditions matching
    Is Selected_Committee a Variable or simply a placeholder in your post ? If a Variable, you do not need quotes "" around it
    ClearCollect(
       Committee_col, 
       Filter(
          'Actions List', 
          Project_or_committee_name = Selected_committee
       )
    )
    however if you are putting in the actual value in quotes, then the query is Delegable and your list size or Data Row Limit should not matter. One question (not exactly sure how it is relevant presently), but do the ones without spaces in the name work and the ones with do not ?
     
    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    Buy me a coffee

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

End of Year Newsletter…

End of Year Community Newsletter…

Tuesday Tip #12 Start your Super User…

Welcome to a brand new series, Tuesday Tips…

Tuesday Tip #11 New Opportunities…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 144,654

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,440

Leaderboard

Featured topics

Loading started
Loading complete