cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fabiofa
Frequent Visitor

Convert entire table to upper/ lower case OR filter array & excel list excluding character casing

Hello All,

 

I am having some trouble filtering two lists.

My flow is supposed to do the following:

1- Fetch a list of members from Outlook group

2- Fetch a list of users from an excel table

3- Convert the Outlook members lost into an array of e-mails and lower case all entries

4- Filter and compare the excel list with the array of Outlook group e-mails

5- Add to an Outlook group the list of users from Excel who are not present in the outlook group previously fetched in step 1 

 

The step I get stuck at is #4 I'm unable to properly filter the list

 

Example Input

Outlook Group MembersExcel List
john@microsoft.combilly@Microsoft.com
billy@microsoft.comJOHN@microsoft.com
sarah@microsoft.comlaura@microsoft.com
bob@microsoft.comsarah@microsoft.com

 

The Outlook group list is all lower case and in an array.

The Excel list has mixed casing.

After filtering the two lists I should be left with: 

laura@microsoft.com   

because that entry does not match anything in the outlook group list

 

This is the filter I am using 

 

Capture.PNG

 

Outputs are the following:

What is contained in the lowercase array of group members

Capture.PNG

 

the input and output after performing the filter 

Capture.PNG

 Since the e-mail is upper case the filter is not working properly, john@microsoft.com  is already present

 

Please let me know if I am doing the filtering wrong or how would I go about converting an entire excel table into lower case so I don't have to perform lowercase filtering in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

Hi @fabiofa 

 

Try this...

 

DamoBird365_0-1637346698877.png

 

 

No need for an apply to each, make sure you swith the Select to Text Mode.  This gives you an array of emails in lower case to compare with the emails of the Excel Table.

 

I have an excel table

DamoBird365_1-1637346762550.png

You can see my input and output here:

DamoBird365_2-1637346795775.png

With only John Present.

 

Your other option is to create an array of lower case emails from teh value of excel using a select.

DamoBird365_3-1637346938700.png

You then end up with the following:

DamoBird365_4-1637346955802.png

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

 

 

 

View solution in original post

1 REPLY 1
DamoBird365
Super User
Super User

Hi @fabiofa 

 

Try this...

 

DamoBird365_0-1637346698877.png

 

 

No need for an apply to each, make sure you swith the Select to Text Mode.  This gives you an array of emails in lower case to compare with the emails of the Excel Table.

 

I have an excel table

DamoBird365_1-1637346762550.png

You can see my input and output here:

DamoBird365_2-1637346795775.png

With only John Present.

 

Your other option is to create an array of lower case emails from teh value of excel using a select.

DamoBird365_3-1637346938700.png

You then end up with the following:

DamoBird365_4-1637346955802.png

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

 

 

 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (2,969)