filter out blank excel rows

[amcneil32]

I have a flow that needs to read from each row in an excel table and send an email for each row. The table size varies daily so I created a range to accommodate that. However, this results in blank rows and gives me the below error. I need a way to filter out blank rows. If the first column has a blank value then the entire row will be blank. I can't figure out why my Filter Array step doesn't achieve this. Any thoughts are appreciated. 

[tom_riha · Accepted solution]

Hello @amcneil32 ,

I think you're making the filter too complex, you can use filter 'column is not equal to empty value' (leave the right side blank).

[amcneil32]

Hey @tom_riha,  thanks for the reply. It doesn't appear I can filter for any columns from the excel table.  They aren't showing up as a dynamic content option. My only options are value and body from the "list rows present in a table" action. I tested this out and still received the same error. Looks like the error indicates the array was never filtered and still has nulls. 

[tom_riha]

Hello @amcneil32 ,

where did you get the item()?['Pro Number'] part in the expression before if the dynamic content is not available? 

[amcneil32]

thanks Tom, the column is now being recognized. I used the expression below and that seemed to work.

[webbrewer]

@tom_riha  So will this filter out any empty rows so they're not included in the table?

[tom_riha]

Hello @webbrewer ,

the output from 'Filter array' will be only the rows with a value, all empty rows will be removed.

[webbrewer]

@tom_riha The output from the filter array indeed contains just the rows with content, but I get a weird error on the create item in SP list step. Any idea what this means?

Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '32921': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

[tom_riha]

Hello @webbrewer ,

the error tells you that you're using a composed expression that contains int(...) expression to convert some value from the Excel file into number, but the input is not a whole number. My guess would be that you're trying to copy date from Excel into SharePoint, but the date has also time part => it'll send a number with decimal places to the flow => int(...) can't be used on numbers with decimal places.

If the time is not important and you need just the date, you can remove the decimal part from the number and then convert it to int(...).

instead of:
...int(items('Apply_to_each')?['StartDate'])...

use:
...int(formatNumber(float(items('Apply_to_each')?['StartDate']),'#'))...

If the time is also important to you then I don't know right now how to take it, I never needed that before. It'll need some other processing of the Excel date value.

[webbrewer]

@tom_riha Strange because the error only seems to show on the "failed" items ie the empty rows excluded by the filter array which implies the filter is still passing empty rows. But if I look at the output of the filter, only rows with data are present so that's all the create item should be trying to create. In this example, there are 6 rows of data in the file and when you look at item 7 (through 99). I get the error. The 6 items with data are created fine.

Curious why it's trying to process rows that have already been filtered out?

[tom_riha]

Hello @webbrewer ,

then check if you're processing in the 'Apply to each' only the items from the output of 'Filter array', that you're not looping through the original array.

[webbrewer]

This is the structure  - filtering out anything with an empty "user name" field and passing rows with data to the next step:

[webbrewer]

Maybe I should add a condition before the create item action that checks for empty rows?

[tom_riha]

Hello @webbrewer ,

can you share the screenshot of the 'Create item' action?

[webbrewer]

I now see there's an option to use "item table entity" (from the filter) rather than "value" (from the file) as in the below screenshot - so trying that. 

[webbrewer]

Changed the output used in the create item/apply to each to "body" from the filter array and that seems to work. Makes no sense that the raw input to the create item action when using the excel file "value" did not include any empty rows, but nothing is straightforward with Msft.