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