Search Email Subject For Keywords, But Foldable

[Julian-K]

Hi!

I want to search a mailbox (Microsoft Exchange) for mails with certain keywords in the subject.

Mailbox = Exchange.Contents("mailadresse@domain.com"),
Mails = Mailbox{[Name="Mail"]}[Data],
Filtered = Table.SelectRows(Mails, (Mails) => List.AnyTrue(List.Transform(Keywords[Column1], each Text.Contains(Mails[Subject], _))))

My problem: The query does not fold, so it takes forever.

It's really quick, if I hard code the keywords like this:

Filtered = Table.SelectRows(Mails, each Text.Contains([Subject], "keyword1") or Text.Contains([Subject], "keyword2") or Text.Contains([Subject], "keyword3"))

But I need the keywords to be dynamical.

Is there a way to make this query foldable, so that Power Query only downloads the mails that match the filter, instead of downloading all the mails and filtering them after?

Best regards,

Julian

[AlexisOlson · Accepted solution]

I'm not sure if it's possible to write the M for this so that it folds into a native Exchange query. You might need to at least download all of the Subject lines.

I'm not sure if something like this will work but it might be worth a try.

Mails = Exchange.Contents("mailadress@domain.com"){[Name="Mail"]}[Data],
Keywords = List.Buffer(Keywords[Column1]),
Subjects = List.Buffer(List.Distinct(Mails[Subjects])),
FilteredSubjects =
    List.Buffer(
        List.Select(
            Subjects,
            (S) => List.AnyTrue(
                       List.Transform(Keywords, each Text.Contains(S, _))
                   )
        )
    ),
Filtered = Table.SelectRows(Mails, each List.Contains(FilteredSubjects, [Subject]))

Note that FilteredSujects might need to be a separate query and you just use the first and last lines of the above to attempt to create a foldable query.

[Julian-K]

Hi Alexis,

I thought about something like this as a fallback myself. Basically the query for just the subjects would be non-foldable, but at least it's as little data as possible and therefore significantly faster. Then I know the subjects so I can proceed with a foldable query.

So I take it that this is just not possible? 😞

Best regards,

Julian

[AlexisOlson]

It might be possible but it's hard to predict folding beyond simple filters and transformations.

Have you considered writing a native query manually rather than trying to get Power Query to translate it for you?

[Julian-K]

How do I write a native query for Exchange? I already googled and couldn't find anything.

That would of course be an option. I already did something similar once for an SQL query, where I basically just concatenated strings in Power Query to build a native SQL query. 😂

[AlexisOlson]

Good question. That suggestion wasn't carefully thought out. I'm not seeing a way to View Native Query when connected to Exchange and it doesn't look like Exchange.Contents() supports a custom query like Sql.Database() does. Even running diagnostics under the Tools tab doesn't show anything Data Source Query column, so it's pretty opaque what sort of native query is happening.

Side note: Microsoft Exchange does not support Direct Query

[Julian-K]

Hey Nate,

thanks for your reply, but if I understand your code correctly, it selects mails where the subject is in the list - by comparing the entire subject to the list elements.

I don't know the entire subject beforehand. I want to select the mails where the subject contains certain keywords.

Best regards,

Julian

[Anonymous]

Instead of using a custom function try storing your keywords as a list (an actual list query), and then instead of each Text.Contains, use Table.SelectRows(TableOrPriorStepName, each List.Contains(List.Distinct(List.Buffer(ListQueryName)), [Subject]))

That might speed it up--and might speed it up more than 100x. Plus it will fold.

--Nate