Power Automate -Conditional sorting with an Excel table that contains the filter contents

[Kevin02]

I have an Excel spreadsheet that has different numbers, these are unique and cannot be assigned multiple times. Depending on which number is in the subject of the email, the Excel table is to be filtered and the conditions are to be executed.

However this happens:
"Unable to process template language expressions for action 'Bedingung' at line '0' and column '0': 'The template language function 'contains' expects parameters of matching types: a dictionary and a key (string), an array and a value (object), or a string and a substring. The provided types 'String' and 'Array' are incompatible. Please see https://aka.ms/logicexpressions#contains for usage details.'."

I am a complete newbie and dont know how to solve it.

Kind regards and many thanks in advance

Kevin

[v-xiaochen-msft]

Hi @Kevin02 ,

Please try this:

Best Regards,

Wearsky

[Kevin02]

Hi @v-xiaochen-msft ,

sorry for my late reply.
It kinda works now - i dont get an error-message but instead he flags every condition as "false" if there isnt just the number in the subject.

Do you know a workaround for that too? 😄


Thanks alot anyways!

Best Regards,

Kevin

[v-xiaochen-msft]

Hi @Kevin02 ,

Sorry, could you explain it in detail?

Best Regards,

Wearsky

[Kevin02]

@v-xiaochen-msft 

As an example for the subject:

1234567

- this works

1234567 abc

-this doesnt work.
I cant control the subject, I just have specific numbers (5.000+) that are in the subject, but there are more "random words". This PowerAutomate setup just works if there is just the number in the subject.

Thanks for the fast reply again!

Best Regards,

Kevin

[v-xiaochen-msft]

Hi @Kevin02 ,

Based on your description, are "random words" not in the excel sheet?

If my assumption is correct, we can remove random words.

Do subjects have the same format or the same rules?

Best Regards,

Wearsky

[Kevin02]

Hi @v-xiaochen-msft ,

In the Excel file/table, only the required numbers are listed, which are then to trigger actions. However, the email subject contains other words, which are all arranged randomly, so you cannot directly filter the numbers.

I would have thought that PowerAutomate would "scan" the subject directly and then perform further actions based on the numbers in the Excel.

Best Regards,

Kevin

[v-xiaochen-msft]

Hi @Kevin02 ,

If the subject has a fixed format, then we can easily extract the numbers.

For example:

123-ABC

456-DEF

Numbers in the first, dashes in the middle, strings in the last.

Does your subject have a format?

Best Regards,

Wearsky

[Kevin02]

Hi @v-xiaochen-msft ,

sadly that isn't the case.
Here are some examples:

abc: def ghi 123 jkl
123 abc def (other random numbers)
abc: def ghi jkl 123

Best Regards,
Kevin

[Kevin02]

@v-xiaochen-msft me again,

maybe we can solve the problem with RegEx? That came in my mind - I had that in university.

Kind Regards,

Kevin

[v-xiaochen-msft]

Hi @Kevin02 ,

Of course we can use RegEx. But power Automate dosen't support it directly.

As a workaround, we call use flow call the excel script to run the RegEx. (need office 365 license)

And we can use Ai builder in flow to extract the number from the text. But it's not free.

Best Regards,

Wearsky

[Kevin02]

Hi @v-xiaochen-msft ,

thats sounds interessting but I don't have capacity for AI Builders, I'll request some from my employer - but I don't know if I get some.

Do you know another possible solution for that?

Best Regards,
Kevin

[v-xiaochen-msft]

Hi @Kevin02 ,

Please try this:

range(0,length(variables('var')))

string('1234567890')

slice(variables('var'),items('Apply_to_each'),add(items('Apply_to_each'),1))

items('Apply_to_each')

Best Regards,

Wearsky

[Kevin02]

Hi @v-xiaochen-msft ,

do I need to attach this to my other flow?

Best Regards,

Kevin

[v-xiaochen-msft]

Hi @Kevin02 ,

Yes, this is an example to extract a number from a string.

Please imagine the value of the variable as the value of the subject.

Best Regards,

Wearsky

[Kevin02]

Hi @v-xiaochen-msft ,

I think I took a wrong turn somewhere, I don't know how to implement the Excel-table.

Thats what I have:

Best Regards,

Kevin

[Kevin02]

I still have problems to solve the "riddle" - does anyone knows how to solve it? 😉 

Kind Regards,

Kevin

[Kevin02 · Accepted solution]

This is my solution:

If you want this condition to be true, if there is just ONE match in the subject - you need to change the condition to "is equal" to 1