Unanswered

Filter query of excel list rows on month and year of a date column

(0) Share

Report

Posted on by Theodora

Hello,

I am trying to build a flow that sends a reminder once a month to all of the people that have a DueDate this month. I have set a Recurrence flow that works, but I am trying to use variables to avoid adding a 'Reminder Date' column to my excel table.

To do so I have set a variable that gets me the current month and year :

formatDateTime(convertFromUtc(utcNow(),'Romance Standard Time'),'MM-yyyy')

Then in my List rows, I have this filter query :

'formatDateTime(item()['DueDate'],'MM-yyyy')' eq CeMois

However I get this error message :

Unable to process template language expressions in action 'List_rows_present_in_a_table' inputs at line '0' and column '0': 'The template language expression 'formatDateTime(item()['DueDate'],'MM-yyyy')' cannot be evaluated because property 'DueDate' cannot be selected. Please see https://aka.ms/logicexpressions for usage details.'.

Is there a way to fix my mistake ? I know that my flow works without a variable by adding a reminder date column and comparing it to today's date, but I would like to minimize the data preparation part.

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

SwJ23 85 on at

Like (0)

Report

Well, there may be something else wrong, but you're missing a "?" in your query:

'formatDateTime(item()['DueDate'],'MM-yyyy')' eq CeMois

Should Be:

'formatDateTime(item()?['DueDate'],'MM-yyyy')' eq CeMois

That "?" from what I can tell is how you drill down in an object to select a property.

Also, and this may be more important, what item() are you selecting? I don't see an Apply to Each loop in here, and item() isn't necessary otherwise. If you're trying to filter the excel table by row, you put column names in there. Your query may very well be this, or something similar:

'formatDateTime(['DueDate'],'MM-yyyy')' eq CeMois

And at worst, you could manipulate the CeMois to match the format of DueDate column so that your query is more simple:

DueDate eq '@variables('CeMois')

Was this reply helpful? Yes No
Theodora 4 on at

Like (0)

Report

Hi @SwJ23 , thank you for your answer! 😊
I did not know what the question mark was for in this type of query, so thank you for the information 😁
I tried using your solutions, but I got other types of errors.

When using this query:
'formatDateTime(item()?['DueDate'],'MM-yyyy')' eq CeMois
I get the following error :
Error details: One or more fields provided is of type 'Null', a different type is expected.

I have checked and my 'DueDate' column is of Date format in excel, I do not understand this error.

When trying to use this query :
'formatDateTime(['DueDate'],'MM-yyyy')' eq CeMois

I get and error saying the expression is invalid, I had tried this expression and gotten the same error before so I figured that I had to put Item in front of the column name for the query to work.

Lastly I need the full 'DueDate' in my table because I am sending a reminder with the full date in it, and the day of the task is important, so I don't think I can match it to the format of the variable.

Was this reply helpful? Yes No
SwJ23 85 on at

Like (0)

Report

Here's the part I dont know (yet): how to put a column name inside a function in the expression builder for a filter query.

I tested a filter query in excel connector as so:
against this table

and got this JSON data from the outputs:

So I know that writing the column name into the filter query works fine. Now to sort out how to nest that inside a function (this is why my last suggestion of having the column name be plain and adjusting what you filter against)

Was this reply helpful? Yes No
SwJ23 85 on at

Like (0)

Report

Try using the expression below:

Since your due date just needs to contain the month and year, you can just use the due date in whatever its native format is and format the CeMois date to match part of the due date string.

ie

DueDate = 01/07/2023
CeMois = formatdatetime(convertfromutc(utcnow(),'Romance Standard Time'),'MM/yyyy'))

In other words, you might be able to use contains instead of a more direct filter like equals.

If you can show me the native format of DueDate I might think up something else.

Was this reply helpful? Yes No
Theodora 4 on at

Like (0)

Report

Hi @SwJ23 ,
Thank you so much for your help!
I tried the version using contains, and there is no error anymore (🎉!) however the query filters out my entire table and I have no data, so there must be a problem in finding the month and year in the date.
I assumed that if I kept the ISO format 'yyyy-MM' it would work because the date format of the list rows from table is ISO.
When it didn't work I adapted the format of my variable to the format of my Excel column ( 'MM/yyyy')
Here is my due date column, it is a Date format here it is a French format but I tried first with the US format (6/23/2023).

My real Excel table will be generated with Power BI so I guess I could give it any format I want.

I think that I will have to add the reminder column to the original table, because I know it works and I thought using a more precise filter query would be easier than this, but I really don't have enough knowledge in Power Automate to do it 😅

Was this reply helpful? Yes No