Answered

Fuzzy or approximate matching in Power Automate

Like (1) Share

Report

Posted on 22 Dec 2021 21:33:09 by Community Power Pla...

I have a flow that automatically updates an end users information in an excel table based off a MS Forms submission. However, right now the inputted email has to be an exact match for it to update. For example, if in my excel their email (key column) is example@gmail.com but in the MS Forms they input (key value) Example@gmail.com it won't be able to update - because of the capitalized E.

This is also the same problem if they add a space before or after the email. Is it possible for Power Automate to find the email besides these small inconsistencies?

All responses (5)

Answers (1)

johnvermeulen 2 on 28 Jul 2022 at 19:41:55

Like (0)

Report

Re: Fuzzy or approximate matching in Power Automate

Converting the Key Value to lowercase is all well and good, but I need to do the same for the Key shown below.

Let's say the key found in the Key Column 'email' is "John.doe@gmail.com" and the Key Value is "John.Doe@gmail.com". Using toLower() on one does not get me any closer to matching the two.

I am not creating this flow for myself and I need a solution robust enough to handle simple case mismatches without iterating through the entire excel table.
Community Power Pla... on 10 Jan 2022 at 14:42:07

Like (0)

Report

Re: Fuzzy or approximate matching in Power Automate

Thank you!
Verified answer

AnthonyAmador 2,553 Moderator on 07 Jan 2022 at 20:45:47

Like (1)

Report
Re: Fuzzy or approximate matching in Power Automate

Hi @Anonymous

To add dynamic content to the expression, type the expression, then click the Dynamic content tab again and select the Dynamic content you need, for example.

The expression should be look like this.

toLower(outputs('Get_response_details')?['body/rfa0c5b1518a64ec884320a184fe0f950'])

I said "equal to" to "contains" because a thought you were using a condition, however I think the trim() function could help you remove the spaces in the string.

So in the end the expression should be:

trim(toLower(outputs('Get_response_details')?['body/rfa0c5b1518a64ec884320a184fe0f950']))

Just don't copy and paste it because the dynamic content from your form is different.

Hope this helps.

Regards.

Community Support Team - Anthony Amador

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Power Pla... on 07 Jan 2022 at 18:35:12

Like (0)

Report

Re: Fuzzy or approximate matching in Power Automate

Thanks for your comment.

So, to clarify. The screenshot below is what I am trying to update

Are you saying in the Key Value section where it currently tries to match the "Dynamic Content" Email from MS Forms. I should put an expression that says something like toLower(Email)?

How exactly can that be done using the expression ? When i begin typing toLower() in the expression window, I can't figure out how to add the "Dynamic Content" Email to the expression. On top of that, how do you make change it to contains instead of equal to ?

Thanks
AnthonyAmador 2,553 Moderator on 28 Dec 2021 at 21:51:21

Like (1)

Report

Re: Fuzzy or approximate matching in Power Automate

Hi @Anonymous

You can try to convert the key value to a single case, using either the toUpper() or toLower() expressions, and then compare against a single case string, for spaces problem you should use "contains" instead of "equal to" in the comparison.

Hope this helps.
Kind regards.
Community Support Team - Anthony Amador
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.