Forum Discussion
Search Email Subject For Keywords, But Foldable
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.
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?
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. 😂
