How to formulate a filter with Surname field is empty and don't display in Excel in Power Automate

[Rexcel]

How to formulate a filter with Surname field is empty and don't display in Excel in Power Automate building flows

I am trying to display all the users in O365 AD without having Surname or "" or empty string

Only active users with First name only

How to do that using power automate using flows

Already tried this equation like surname is equal to null but still keep showing the users without surname/empty string after generate the file into excel

Please help if any need to add or condition to fix my issue

I try to run again and the output still the same

It should be not display the users without surname or blank

[Srini007]

Hi @Rexcel 

Check below link for your reference

https://powerusers.microsoft.com/t5/General-Power-Automate/Remove-Null-in-Table/td-p/1969350

As you mentioned that you already tried the solution but make sure that the column name is exactly same as surname. Check if the first letter is S/s

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Rexcel]

Hello SRini,

Still not working and yes my filter was correct under Surname

and this is my flow in the beginning

It keeps showing the users having null or empty surname after generate

see below for my flow on "add a row in a table"

[Srini007]

@Rexcel 

Okay, then can you try placing another Filter array this time Surname is not null same as you the one you keep for the Job Title and try

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Rexcel]

Looks like this?

If yes, the generated file still showing the users with surname empty string/blank.

Also I tried using condition but still showing it 😞

[Rexcel]

Still showing it 

[Sundeep_Malik]

Hey @Rexcel 

I see above you have used two separate filter arrays for job title and surname.

Instead do this:

1st filter array to filter job title not equal to null:

2nd Filter Array:

In From add the body of filter array of job title and in item add: item()['Surname']

I hope this helps 🙂

[Rexcel]

Hi @Sundeep_Malik 

It looks like this?

But still not working

[Sundeep_Malik]

@Rexcel 

I also did the same and it showed the same result.

Instead of adding item()['Surname']

and a question mark between the normal brackets and square brackets.

Do this:

item()?['Surname']

This will definitely work.

Not exactly sure why it didnt work without a ?, it should have.

I hope this helps 🙂

[Rexcel]

Hi @Sundeep_Malik 

The flow successfully run and tested but the generated output/result was still the same

There's still users without surname 

This is the existing flow

[Sundeep_Malik]

@Rexcel 

Can you send screenshot of what are you adding in Excel like what expression are you adding?

[Rexcel]

@Sundeep_Malik 

This one?

[Sundeep_Malik]

@Rexcel 

The value that you are using is adding all the users. Thats why this is happening.

Instead of this, add the latest filter array body in it.

And then in add a row into a table write this:

items('Apply_to_each')?['Surname']

and for all other values too.

Actually you are adding all the values by using values of search for users in apply to each, instead you have to add the body of the filtered array, that why you are getting users with null surnames.

Before this I hope in other filters you have done the same thing which I told you.

Using the body of the above filter and writing item()?['Whatever field you want to add']

If not in those do it too and then use body of last filter in the apply to each.

Some days back same thing happened with my colleague I told her to use body and she used value of search users and instead of sending mail to filtered people she sent an email to every person in the organization. 😂

I hope this helps 😉

[Rexcel]

@Sundeep_Malik 

So my flow turns to this

or do I need to change also the "value" from the first filter array "filter array 2" to Body?

If yes, doesn't have any "body" options

[Sundeep_Malik]

@Rexcel 

No this looks fine.

The first filter array which is filter array 2 will have value only which will come from search for users.

So it looks correct.

Run it once and let me know if it's working or not.

Hopefully it should, if not there could be minor errors.

[Rexcel]

@Sundeep_Malik 

after the testing

the generated results like this and there's still users without surname

Also I notice that some users duplicate on the generated output

[Sundeep_Malik]

@Rexcel 

Just confirming.

Filter Array 2 

->Value coming from Search Users

Filter Array 4

-> Body coming from Filter Array 2

Filter Array 3

-> Body coming from Filter Array 4

-> Expression used item()?['Account_Enabled'] something. (Could be different)

Filter Array

-> Body coming from Filter Array 3

-> Expression used item()?['User Principal something']

Then in apply to each

Body coming from Filter Array

And user ID used is 

items('Apply_to_each')?['User_ID'] something

Check once and change the respectiv values.

[Rexcel · Accepted solution]

@Sundeep_Malik 

That's correct (please see my flow via avdvanced mode)