Answered

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

(0) Share

Report

Posted on by Del-Dev

160

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!

Categories:

Using connectors

I have the same question (0)

All responses (26)

Answers (1)

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
Del-Dev 160 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
Del-Dev 160 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
Del-Dev 160 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
Del-Dev 160 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
Del-Dev 160 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No