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

7 REPLIES 7
ChristianAbata
Super User
Super User

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.

vernariesaco
Frequent Visitor

Thank goodness I found your post. I am running into the same problem. I will try your solution... Thank you!

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

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

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (5,039)