Forum Discussion
Search Email Subject For Keywords, But Foldable
Hi!
I want to search a mailbox (Microsoft Exchange) for mails with certain keywords in the subject.
Mailbox = Exchange.Contents("[email protected]"),
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
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("[email protected]"){[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.
7 Replies
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
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
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("[email protected]"){[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.
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
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?
