cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Filter Array is blank after Excel List Rows shows a valid record.

 Everything I've read and know about Flow is lost on me today.  I am pretty certain i have set up this flow correctly but the Output of the Filter Array is always empty even though the Filter Query in the List Rows action finds a record. 

 

Int the List Rows action, I filter out all rows that have the current month in the BMonth field.  I use an expression for Current_Month because it is a number column in my spreadsheet (I extrapolate the month from a date field and put the number of the month here)

bday flow3.png

 

bday flow.png

 

 

After running the flow, i get an output from the Filtering of the Excel List Rows which includes a record with a

BMonth eq Int(formatDateTime(utcNow(), 'MM')

 

Then the Filter Array is supposed to filter that output and output the record where

BDay eq Int(formatDateTime(utcNow(),'dd'))

 

Which one is clearly present but the output of the Array is always blank [].

 

bday flow2.png

 

I have tried making the columns for these numbers Text, Number, General... using "Int" in the expression or not using it makes no difference.  Using a Compose action or not. Using single quotes or not. and finally, hard coding the number 5 in the condition as in: BDay is equal to 5   (or '5')  to no avail. 

 

 

 

Is it because it's a number or am i seriously doing something wrong?   Thanks in advance for your help guys! 

 

UPDATE: I found that if i have the Filter Array condition on a text field that has alpha characters, it works fine. It is only when it tries to filter a number from either a number or text column that it results in blank.  So I guess the real question is:  Do Filter Arrays not work for numbers? And, how can I 'trick' it into seeing a number as text? (The column format in excel is already set to Text but with a number only in a text field, Flow still sees it as a number and fails). Thanks!

 

Joe

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Filter Array is blank after Excel List Rows shows a valid record.

Hi @jcollins ,

 

After troubleshooting this in a million different ways, it seems that the Filter Array action is not able to handle/recognize numerical string values. The object coming over from Excel is formatting the BDay as a string meaning you would need to not do the conversion to int() in the array.

 

You would set BDay equal to formatDateTime(utcNow(), 'dd')

 

The problem with this is that any day that is a single digit comes over from the above formula os 01, 02, 03, etc. meaning the BDay column in Excel would need to be the same 01, 02, 03, etc. The problem with that is that it seems Excel doesn't let you type a zero in front of a digit while the data type of the cell is number, so you would need to convert the column to a string to type in the BDay. 

 

This is the only workaround I have figured out so far, but there could be another way. Unfortunately, it seems like the Excel connector needs to be able to send an integer and keep it an integer, and the Filter array action needs to be able to recognizing numerical strings.

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Filter Array is blank after Excel List Rows shows a valid record.

Hi @jcollins ,

 

Can you tell me what the full expression that starts with int() that you are using to filter is? For both the one in the filter query and the one in the filter array.

Highlighted
Advocate II
Advocate II

Re: Filter Array is blank after Excel List Rows shows a valid record.

was in between the two images: 

 

After running the flow, i get an output from the Filtering of the Excel List Rows which includes a record with a

BMonth eq Int(formatDateTime(utcNow(), 'MM')) 

 

Then the Filter Array is supposed to filter that output and output the record where

BDay eq Int(formatDateTime(utcNow(),'dd'))

 

These provide an integer for the content being passed from the spreadsheet but the spreadsheet column is already set to a Number so it should already be an Integer.   I have tried with and without the "Int" in the expression and same result. 

Highlighted
Microsoft
Microsoft

Re: Filter Array is blank after Excel List Rows shows a valid record.

Hi @jcollins ,

 

After troubleshooting this in a million different ways, it seems that the Filter Array action is not able to handle/recognize numerical string values. The object coming over from Excel is formatting the BDay as a string meaning you would need to not do the conversion to int() in the array.

 

You would set BDay equal to formatDateTime(utcNow(), 'dd')

 

The problem with this is that any day that is a single digit comes over from the above formula os 01, 02, 03, etc. meaning the BDay column in Excel would need to be the same 01, 02, 03, etc. The problem with that is that it seems Excel doesn't let you type a zero in front of a digit while the data type of the cell is number, so you would need to convert the column to a string to type in the BDay. 

 

This is the only workaround I have figured out so far, but there could be another way. Unfortunately, it seems like the Excel connector needs to be able to send an integer and keep it an integer, and the Filter array action needs to be able to recognizing numerical strings.

View solution in original post

Highlighted
Advocate II
Advocate II

Re: Filter Array is blank after Excel List Rows shows a valid record.

Thanks!  so I solved this my adding a formula to the BMonth (and BDay) column in the spreadsheet to check for the length of characters in the Month.  If only 1, then add a "0": 

 

=IF(LEN(MONTH(B2))=1,CONCAT("0",(MONTH(B2))),CONCAT(MONTH(B2)))

 

Then, when my Filter Array, which now had a "02" being passed in from the spreadsheet, would equal the same format as the 

formatDateTime(utcNow(),'MM') that was being composed and wala, they were indeed both equal. 
 
Thanks for the direction! 
 
Joe

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (6,624)