cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeddPearce
Regular Visitor

Excel List Row Filter Query Broken?

I've got an Excel sheet with a bunch of rows grouped by a specific column. E.g.

GroupDateIDProduct
41Watermelons
42Apples
42Bananas
42Cherries
42Dates
43Elderberries

I have a flow that uses "List rows present in a table" operation, with a filter query of "GroupDateID eq '42'". This should always return a list of Apples, Bananas, Cherries, and Dates. Except sometimes it returns Apples and Dates, sometimes Bananas and Cherries, and sometimes it doesn't return anything at all. If I remove the filter query entirely, they all return (along with all the other IDs I don't want), but it's getting the data. It's only when I filter that I get weird results.

To make matters stranger, I didn't see any problems with this until several weeks ago. Then the filter results started getting inconsistent, and now the filter results are always null. 

Was there an update that broke the filter functionality? Is my ODATA query somehow wrong? What on earth could be happening here?
EDIT: Here's a weird scenario I found: If I go back to an earlier GroupDateID that worked previously and edit the contents of that row, the edited row is found by the operation with the changes. But if I copy that row, it only finds the original row not the duplicate (even though their contents are exactly the same). I'm flummoxed.

1 ACCEPTED SOLUTION

Accepted Solutions
JeddPearce
Regular Visitor

I hired Microsoft support to look into the issue, and they have no idea what caused the problem, but the solution was clear and reconnect the operation. Once everything was reconnected it pulled the data as intended.

View solution in original post

6 REPLIES 6
ChristianAbata
Community Champion
Community Champion

hi @JeddPearce  to compare string or Text you have to add '' but for number is wihout

Numbers = GroupDateID eq 42

Text = GroupDateID eq '42'



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA
JeddPearce
Regular Visitor

I simplified it for this thread, but this is the expression I'm actually using:

 

 

StartDateID eq '@{div
(
  sub
  (
    ticks(formatDateTime(utcNow(), 'yyyy-MM-ddT00:00:00')), 
    599264352000000000), 
  864000000000
)}'

 

So it's producing the result of:

 

StartDateID eq '44638'

 

But even if I manually try it with "StartDateID eq 44638" I don't get any results (today there are 14 results that should match).

KvB1
Solution Sage
Solution Sage

Pretty sure the expression you use will return a number, and the value you are comparing it to is a string

Try: StartDateID eq string(44638)  to check if this is true, and modify your expression to

StartDateID eq '@{string(div
(
  sub
  (
    ticks(formatDateTime(utcNow(), 'yyyy-MM-ddT00:00:00')), 
    599264352000000000), 
  864000000000
))}'

if needed

That gives me this error:

An unknown function with name 'string' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.
     inner exception: An unknown function with name 'string' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.
clientRequestId: 8f6bb1da-aafe-4238-b39c-932bbce79296
KvB1
Solution Sage
Solution Sage

KvB1_0-1647611208224.png

this returns true for me

JeddPearce
Regular Visitor

I hired Microsoft support to look into the issue, and they have no idea what caused the problem, but the solution was clear and reconnect the operation. Once everything was reconnected it pulled the data as intended.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (3,607)