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.
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. 😂