Using a column (Embedded with formulas) as a condition in the Filter Array action

[Del-Dev]

As of now, I'm looking into using a column that is embedded with a formula as a condition in the "Filter Array" action. I can't seem to use the number from that column directly as a condition because it was embedded with a formula. Below is the screenshot of the data from an excel sheet:

I wanted to use [Days2] as my condition in the "Filter Array" action for if [Days2] is > 8, then do something. Below is the screenshot of the formula embedded into the [Days2] column:

This formula is embedded into the [Days2] column and is preventing me to use the value, e.g. 16 as a condition in the "Filter Array" action. Any techniques or methods are appreciated, thanks in advance!

[v-jefferni]

Hi @Del-Dev ,

Excel formulas is not supported in Power Automate. You must calculate the value in Filter array directly using the expressions.

Best regards,

[Del-Dev]

Hi @v-jefferni,

Just to make sure I'm not making any misunderstandings. I'm looking into how to make [Days2] into a condition for if [Days2] is > 8, then do something. Since the first column under [Days2] has a value of 16, it satisfies the condition and then moves on to do something. But there is an expression applied into the columns of [Days2] and is preventing me to use it as a condition in the "Filter Array" action. Therefore, I need some advice for this.

You mentioned that I would need to calculate the value in my flow directly using the expressions, may I ask how would I go about it? Thanks!

[v-jefferni · Accepted solution]

Hi @Del-Dev ,

I mean filter based on the same condition in Filter array:

@greater(if(less(item()['Date'], utcNow('yyyy-MM-dd')), dateDifference(item()['Date'], utcNow('yyyy-MM-dd')), dateDifference(utcNow('yyyy-MM-dd'), item()['Date'])), 😎

Best regards,

[Del-Dev]

Hi @v-jefferni,

According to the screenshot of the expression in the first post of this discussion where the [Days2] column is embedded with the following expression:

Now the [Days2] column is embedded with this new expression: =@IFS(@P:P="Danger", "Completed", P:P="Risky", "Completed", TODAY()-@Q:Q,TODAY()-@Q:Q>=0,TODAY()-@Q:Q). Same as usual, because the [Days2] column is embedded with such expression, I cannot get the value from the [Days2] column directly when comparing it with a condition in my "Filter Array" action, for if [Days2] is > 8.

I have used the above expression that you have suggested: greater(if(less(item()['Date'], utcNow('yyyy-MM-dd')), dateDifference(item()['Date'], utcNow('yyyy-MM-dd')), dateDifference(utcNow('yyyy-MM-dd'), item()['Date'])). I have set the DateTime Format in "List Rows Present in a Table" to ISO 8601 and just that I have changed the [Date] to [Outstanding Date] in my excel column and this is my current expression in my "Filter Array" action with reference to the expression of what you have suggested:

 but I have encountered the error message above. I have encountered this error: 

Does the word "Completed" in this expression: "=@IFS(@P:P="Danger", "Completed", P:P="Risky", "Completed", TODAY()-@Q:Q,TODAY()-@Q:Q>=0,TODAY()-@Q:Q)" play a part in the error or did I miss anything in my expression in my "Filter Array" action?

[v-jefferni]

Hi @Del-Dev ,

The error is saying that a field of 'outstanding date' was "Completed", which is not an ISO 8601 format date. All the values of a single column should have the same format, either date or string.

Best regards,

[Del-Dev]

Hi @v-jefferni,

Ahhhh I seeee! Thanks for the explanation for the error message! I found out that the [Outstanding Date] column of the excel sheet has 2 types of data: Date and the word "Completed". As seen from the [Days2] column embedded with this formula: "=@IFS(@P:P="Danger", "Completed", P:P="Risky", "Completed", TODAY()-@Q:Q,TODAY()-@Q:Q>=0,TODAY()-@Q:Q)", Q:Q is denoted as the data from the [Outstanding Date] and whatever data in the [Outstanding Date] would influence the data in the [Days2] column.

Since the [Outstanding Date] has 2 types of data: Date and "Completed", how do I go about in such a way that I  want calculate the number of days from the [Days2] column from this expression "=@IFS(@P:P="Danger", "Completed", P:P="Risky", "Completed", TODAY()-@Q:Q,TODAY()-@Q:Q>=0,TODAY()-@Q:Q) and if there is a word "Completed" in the [Outstanding Date] column, then no need calculate the number of days in the [Days2] column?

[v-jefferni]

Hi @Del-Dev ,

I think you'd better clear the logic first. You have two types of data in Days2 column as well. What formula do you have in Outstanding Date and Days2 columns exactly? 

Best regards,

[Del-Dev]

Hi @v-jefferni,

Sorry for the confusion! The data in the excel sheet previously was not updated. The below screenshot is the latest data in an excel sheet:

The formulas for [Outstanding Date] and [Days2] are as follows:

Formula for [Outstanding Date]: =@IFS(@E:E="Danger", "Completed",@E:E="Risky","Completed",@B:B="Red1",@A:A+5,@B:B="Red2",@A:A+10,@B:B="Red3",@A:A+15,@B:B="Red4",@A:A+20,@B:B="Red5","Red5 (Keep In View)")

Formula for [Days2]: =@IFS(@E:E="Danger", "Completed",@E:E="Risky", "Completed",@C:C="Red5 (Keep In View)","Red5 (Keep In View)",TODAY()-@C:C<0,TODAY()-@C:C,TODAY()-@C:C>=0,TODAY()-@C:C)

Same as usual, because the [Days2] and the [Outstanding Date] column are embedded with formulas, I cannot get the value from the [Days2] column directly when comparing it with a condition in my "Filter Array" action, for if [Days2] is > 8.

With the formulas embedded in the [Outstanding Date] and [Days2] columns, how do I get the value from the [Days2] column when comparing it with a condition in my "Filter Array" action, for if [Days2] is > 8. Thanks!

[v-jefferni]

Hi @Del-Dev ,

I understood. So, actually the logic would be:

If Status = Danger or Risky, or Level = Red5, ignore, otherwise filter items that Days are larger than 8. 

So, the expression in Filter array would be:

@and(and(not(equals(item()?['Status'],'Danger')),not(equals(item()?['Status'],'Risky')),not(equals(item()?['Level'],'Red5'))), greater(if(less(item()['Days2'], utcNow('yyyy-MM-dd')), dateDifference(item()['Days2'], utcNow('yyyy-MM-dd')), dateDifference(utcNow('yyyy-MM-dd'), item()['Days2'])), 8))

Best regards,

[Del-Dev]

Hi @v-jefferni,

Thank you for your suggestion! So, the expression for the Filter Array that you have suggested could mitigate the error due to the word "Completed" in the previous post?

[v-jefferni]

Hi @Del-Dev ,

It will completely ignore the Completed issue, rows that Days2 column are not a number will not be included when checking the larger than 8 condition.

Best regards,

[Del-Dev]

Hi @v-jefferni,

Oh I see! After running the above expression, I have encountered the following error:

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@and(and(not(equals(item()?['Status'],'Danger')),not(equals(item()?['Status'],'Risky')),not(equals(item()?['Level'],'Red5'))), greater(if(less(item()['Days2'], utcNow('yyyy-MM-dd')), dateDifference(item()['Days2'], utcNow('yyyy-MM-dd')), dateDifference(utcNow('yyyy-MM-dd'), item()['Days2'])), 8))' failed: 'In function 'dateDifference', the value provided for date time string '-6' was not valid. The datetime string must match ISO 8601 format.'.

The error mentioned that the datetime string '-6' does not match with the ISO 8601 format. I do have a -6 in my ['Days2'] column and it represents the number of days. Shouldn't all the negative numbers be ignored since the condition stated that ['Days2'] is > 8? I'm wondering after the calculation of the dateDifference function, should the number of days be in ISO 8601 format and that's why this error is generated? Please advise me on this!

[v-jefferni]

Hi @Del-Dev ,

Sorry, I forgot to calculate the Days2 column value in the expression. Please try below instead:

@and(and(not(equals(item()?['Status'],'Danger')),not(equals(item()?['Status'],'Risky')),not(equals(item()?['Level'],'Red5'))), greater(if(less(addDays(item()['Date of Discover'],if(equals(item()['Level'], 'Red1'),5,if(equals(item()['Level'], 'Red2'),10,if(equals(item()['Level'], 'Red3'),15,if(equals(item()['Level'], 'Red4'),20,if(equals(item()['Level'], 'Red5'),25))))), utcNow('yyyy-MM-dd')), dateDifference(addDays(item()['Date of Discover'],if(equals(item()['Level'], 'Red1'),5,if(equals(item()['Level'], 'Red2'),10,if(equals(item()['Level'], 'Red3'),15,if(equals(item()['Level'], 'Red4'),20,if(equals(item()['Level'], 'Red5'),25))))), utcNow('yyyy-MM-dd')), dateDifference(utcNow('yyyy-MM-dd'), addDays(item()['Date of Discover'],if(equals(item()['Level'], 'Red1'),5,if(equals(item()['Level'], 'Red2'),10,if(equals(item()['Level'], 'Red3'),15,if(equals(item()['Level'], 'Red4'),20,if(equals(item()['Level'], 'Red5'),25))))))), 8))

Best regards,

[Del-Dev]

Hi @v-jefferni,

Woahhhh that's very long hahahah. I put the formulas for the ['Outstanding Date'] and ['Days2'] here for easy reference:

Formula for [Outstanding Date]: =@IFS(@E:E="Danger", "Completed",@E:E="Risky","Completed",@B:B="Red1",@A:A+5,@B:B="Red2",@A:A+10,@B:B="Red3",@A:A+15,@B:B="Red4",@A:A+20,@B:B="Red5","Red5 (Keep In View)")

Formula for [Days2]: =@IFS(@E:E="Danger", "Completed",@E:E="Risky", "Completed",@C:C="Red5 (Keep In View)","Red5 (Keep In View)",TODAY()-@C:C<0,TODAY()-@C:C,TODAY()-@C:C>=0,TODAY()-@C:C)

Based on your suggested expression on the part: if(equals(item()['Level'], 'Red5'),25. I would like to clarify with you that should that be if(equals(item()['Level'], 'Red5', 'Red5 (Keep In View)', based on the Formula for ['Outstanding Date']?

Based on the screenshot of the excel table in the previous post, I put it here for easy reference:

Since C:C is denoted as ['Outstanding Date'], should ['Outstanding Date'] be in the dateDifference function instead of ['Date of Discover']?

In addition, I know it would be a bit tedious, would you mind explaining the thought process of your suggested expression? I just want to make sure that I understands it and correct me if I'm wrong. Thanks a lot!

[v-jefferni]

Hi @Del-Dev ,

Since values of the Outstand Date column are calculated from other 3 columns, I didn't use these values but calculated the values instead, which makes the expression so long.

Explanation to the expression:

@and(
    and(
        not(equals(item()?['Status'],'Danger')),
        not(equals(item()?['Status'],'Risky')),
        not(equals(item()?['Level'],'Red5'))
    ), //ignore rows that status = danger or risky, and level = red5, because all thess rows will make Outstanding date column not a date.
    greater(
        if(
            less(
                addDays(
                    item()['Date of Discover'],
                    if(equals(item()['Level'], 'Red1'),5,if(equals(item()['Level'], 'Red2'),10,if(equals(item()['Level'], 'Red3'),15,if(equals(item()['Level'], 'Red4'),20)))  //the Outstanding Date column values, add different number of days to Date of Discover column based on Level column
                ),
                utcNow('yyyy-MM-dd')
            ),  //check the date if is earlier than today
            dateDifference(
                addDays(
                    item()['Date of Discover'],
                    if(equals(item()['Level'], 'Red1'),5,if(equals(item()['Level'], 'Red2'),10,if(equals(item()['Level'], 'Red3'),15,if(equals(item()['Level'], 'Red4'),20)))
                ), 
                utcNow('yyyy-MM-dd')
            ),  //if yes, today - calculated date 
            dateDifference(
                utcNow('yyyy-MM-dd'), 
                addDays(
                    item()['Date of Discover'],
                    if(equals(item()['Level'], 'Red1'),5,if(equals(item()['Level'], 'Red2'),10,if(equals(item()['Level'], 'Red3'),15,if(equals(item()['Level'], 'Red4'),20)))
                )
            )   //if no, calculated date - today
        ),  // the Days2 column value
        8   //if the difference is larger than 8
    )
)

Best regards,

[Del-Dev]

Hi @v-jefferni,

This is my expression with reference to your suggested expression:

and(
    and(
        not(equals(item()?['Status'],'Danger')),
        not(equals(item()?['Status'],'Risky')),
        not(equals(item()?['Level'],'Red5'))
    ), 
    greater(
        if(
            less(
                addDays(
                    item()?['Date of Discover'],
                    if(equals(item()?['Level'], 'Red1'),5,if(equals(item()?['Level'], 'Red2'),10,if(equals(item()?['Level'], 'Red3'),15,if(equals(item()?['Level'], 'Red4'),20)))  
                ),
                utcNow('yyyy-MM-dd')
            ),  
            dateDifference(
                addDays(
                    item()?['Date of Discover'],
                    if(equals(item()?['Level'], 'Red1'),5,if(equals(item()?['Level'], 'Red2'),10,if(equals(item()?['Level'], 'Red3'),15,if(equals(item()?['Level'], 'Red4'),20)))
                ), 
                utcNow('yyyy-MM-dd')
            ), 
            dateDifference(
                utcNow('yyyy-MM-dd'), 
                addDays(
                    item()?['Date of Discover'],
                    if(equals(item()?['Level'], 'Red1'),5,if(equals(item()?['Level'], 'Red2'),10,if(equals(item()?['Level'], 'Red3'),15,if(equals(item()?['Level'], 'Red4'),20)))
                )
            )   
        ),  
        8  
    )
)

I have included a "?" between the word item() and ['Column'] to some of the lines that doesn't have. I went to save my flow and I have this error:

Did I miss any syntax and is there any lines that I have typed wrongly?

[Del-Dev]

Hi @v-jefferni,

I tried troubleshooting and looking for any syntax errors present in the code above but still cannot find any. I'm not sure where I went wrong and really need your help on this. Thanks!

[v-jefferni]

Hi @Del-Dev ,

The if function requires 3 arguments, but I confused it with the If function in PowerApps. Try below again, please. Sorry for the carelessness...

and(
    and(
        not(equals(item()?['Status'],'Danger')),
        not(equals(item()?['Status'],'Risky')),
        not(equals(item()?['Level'],'Red5'))
    ), 
    greater(
        if(
            less(
                addDays(
                    item()?['Date of Discover'],
                    if(equals(item()?['Level'], 'Red1'),5,if(equals(item()?['Level'], 'Red2'),10,if(equals(item()?['Level'], 'Red3'),15,if(equals(item()?['Level'], 'Red4'),20,0)))  
                ),
                utcNow('yyyy-MM-dd')
            ),  
            dateDifference(
                addDays(
                    item()?['Date of Discover'],
                    if(equals(item()?['Level'], 'Red1'),5,if(equals(item()?['Level'], 'Red2'),10,if(equals(item()?['Level'], 'Red3'),15,if(equals(item()?['Level'], 'Red4'),20,0)))
                ), 
                utcNow('yyyy-MM-dd')
            ), 
            dateDifference(
                utcNow('yyyy-MM-dd'), 
                addDays(
                    item()?['Date of Discover'],
                    if(equals(item()?['Level'], 'Red1'),5,if(equals(item()?['Level'], 'Red2'),10,if(equals(item()?['Level'], 'Red3'),15,if(equals(item()?['Level'], 'Red4'),20,0)))
                )
            )   
        ),  
        8  
    )
)

Best regards,

[Del-Dev]

Hi @v-jefferni,

Thanks for the reply! I noticed that in the 'addDays' function, you have added a 0 beside 20. What is the intended purpose for the 0?