How to set condition if a select output is non empty.

[dejavuaHK]

Hello everyone, I am writing a power automate flows but have some issues when it comes to condition.  I always google it but couldn't find exact answer.  But I come up with an alternative.  

The source file is from an excel file on the company drive, showing the holidays for some trading markets. The first column is the Date and if trading holidays fall on a certain date, it will show "Holiday" or "Closed". 

I simplify the whole flow for the forum.  The flow is to -->import excel --> select "area" in the imported excel --> set up condition "there is any "Holiday" or "Closed" , if "Yes", it returns the string "Yes, there is holiday", if "No", it returns the string "No, there is no holiday".

The main problem is with the condition set up. I intend to use " if the item of "Hong Kong" column from outputs of select is equal to null and the item of "(all other column header)" column from outputs of select is equal to null. But it doesn't work.

wrong condition sample:  outputs('select')?['item/Hong Kong'] is null

I also try empty() is false and length() >0, but can't find the solution.  I check the flow step by step and found the output from select is {"Date":" 05/19/2022","Day of Week":"Thu","Hong Kong":"","Shanghai & Shenzhen":"",,,,,,} It looks like array? I don't know how to modify the condition.

For my alternative method, I use filter function to filter all rows containing "holiday" and "closed" to check whether the filter output is empty.  

Thanks for your help in advance.

[v-liwei-msft · Accepted solution]

Hi @dejavuaHK ，

Could you please tell me if you want to check whether there are holidays in a certain area for a period of time?

You can use the action "filter array" to judge whether a certain area has holidays.

I have made a test for your reference:

Because you have two conditions: "holiday" and "closed", you need to open "Edit in advanced mode" in "fliter array" and enter this formula:

@or(contains(item()?['United State'], 'Holiday'),contains(item()?['United State'], 'Closed'))

Beat Regards,

Levi

[dejavuaHK]

Thanks a lot, Levi. I found the filter array function as well. It works well.

But it seems that without using filter array, if we want to check whether there is one string value in few rows of imported excel.  We can't do it straightforward, right?   My ideal flow is from imported excel --> select (compose few columns in the excel)  --> condition (containing value in Excel cells).

[dejavuaHK]

Thanks a lot, Levi. I found the filter array function as well. It works well.

But it seems that without using filter array, if we want to check whether there is one string value in few rows of imported excel.  We can't do it straightforward, right?   My ideal flow is from imported excel --> select (compose few columns in the excel)  --> condition (containing value in Excel cells).

[v-liwei-msft]

Hi @dejavuaHK ,

You can use select, but you still need to insert a fliter array under Select.

Unfortunately, if you want to insert a condtion directly after using select, this method may not work because the array returned by select is not empty.

I have made a test for your reference:

Best Regards,

Levi

[dejavuaHK]

Yes, thanks for confirming the solution.  I agree with you that I need to insert a filter function at first

[eliotcole · Accepted solution]

I know that you have your answer, but I thought I'd chime in with a couple of quick alternatives that might be just right to consider as an additional solution.

Select Action With equals() Functions

This basically provides you a daily true/false value for every sector if they have nothing.

So, I mirrored your spreadsheet, and then ran this select action. The expression in each cell is the same, except for the column name, for example here, I used 'Hong Kong':

not(
  equals(
    item()?['Hong Kong'], 
    ''
  )
)

From the inside out:

1. item()?['KEY_NAME'] - This retrieves the value of the KEY_NAME, in this case, 'Hong Kong'.
2. equals() - This compares the value of 'Hong Kong' to an empty string. If they are both empty strings, then this returns true.
3. not() - Since we need to know when the cell is NOT empty, this function reverses whatever result the equals() puts out.

That's it.

Select For ANY Holidays

If you would like to go a different route you can use a similar method to @v-liwei-msft 's excellent solution which uses another select, but this time it uses each of those express whether each day has *any* holidays, all in one large expression.

The other Select is on the right

As you can see I have them alongside each other, you could even include 'Any Holidays' in the first. 

This is slightly more complex, so I've put it in a spoiler.

Spoiler

It's just a very big or() function which will return true if any of the functions within return true. 🙂

or(not(equals(item()?['Hong Kong'], '')), not(equals(item()?['Shanghai & Shenzen'], '')), not(equals(item()?['Northbound Trading'], '')), not(equals(item()?['Southbound Trading'], '')), not(equals(item()?['United States'], '')), not(equals(item()?['Japan'], '')), not(equals(item()?['Korea'], '')))

Then the Boolean variable afterwards contains this expression:

if(
    greater(
        length(
            union(
                body('Select_any_holidays'), 
                body('Select_any_holidays')
            )
        ),
        1
    ),
    true,
    equals(
        union(
            body('Select_any_holidays'), 
            body('Select_any_holidays')
        )[0]?['Any Holidays'],
        true
    )
)

• union() - compares the two arrays to each other, and removes duplicates.
• length() - counts the amount of items inside it.
• greater() - checks if a number is greater than another number.
• if() - if the function listed first is true this returns the value of the second part, if it is false, then the last.

So first it compares the Select any holidays array to itself, then removes any the duplicates for a new array. If there were only false results you will have only one item in this array, if there were any true results, there will be two items in the union(). So any number greater than 1 equals true.

So setting the true value is simple, in the second part here.

Lastly, you have to check in the remote possibility that they are all holidays, in which case you will only get one result, but it will be true. So if the number is not greater than 1, then this function will run an equals() check on the first item that the union() creates. If that is true, then there are holidays EVERYWHERE! 😅

---

Finally, I would just add (not said harshly) some advice, and that is to consider a different place to keep this kind of data. It would be much better kept in a SharePoint list or one or two relevant calendars.

[dejavuaHK]

Thanks Eliotcole. This is also a smart solution. 

[eliotcole]

Cheers, @dejavuaHK, do you mind if I also mark it as an answer? It won't negate the excellent one that you've already picked, but it'll just highlight it for others as a secondary choice. 👍

I don't mind if not, I'm not chasing status here (😅), it's just nice for folks to have the options.