Forum Discussion
Faster mail load from Exchange
Hi everyone,
I’m working with Power Query and using the Exchange.Contents connector to access my Outlook mailbox. The issue is that my mailbox has over 400,000 emails, and Power Query tries to load the entire Mail table before applying any filters — even if I only want data from a specific folder like Inbox/SubFolder.
I’ve tried filtering by FolderPath and DateTimeReceived, but these filters only apply after the full dataset is loaded into memory. This results in extremely slow performance and frequent crashes.
What I need is a way to restrict the number of rows loaded from Exchange before filtering — ideally something like:
Load only a specific folder (e.g., Inbox/SubFolder)
Limit to the most recent 10,000 rows
Apply server-side filtering or pagination
Is there any way to do this natively in Power Query, or is this a limitation of the Exchange.Contents connector? If not currently supported, I’d love to see this added, it would make working with large mailboxes far more practical.
Thanks in advance!
You may want to look into querying your mail messages via Graph API.
Some caveats:
- I have not used this directly in a while, so just going off memory / quick research / testing with Graph Explorer sample tenant.
- Graph integration with PBI is quite limited. Last I checked, though, you could still connect to personal mail through simple http calls and using PBI oauth. See MS party line on this: https://learn.microsoft.com/en-us/power-query/connecting-to-graph
- For a more enterprise solution, you may want to look into the MS recommended route of Graph Data Connect through Data Factory
Connecting would look something like this:
Web.Contents( "https://graph.microsoft.com/v1.0", [ // can query me/mailFolders and nav via childFolders; eg me/mailFolders/<id>/childFolders/<id>/messages RelativePath = "me/mailFolders/inbox/messages", Headers = [ accept = "application/json" ], Query = [ #"$orderby" = "sentDateTime desc", // props list for select: https://learn.microsoft.com/en-us/graph/api/resources/message#properties #"$select" = "id,sentDateTime,from,subject,bodyPreview", // eg filter to this year; datetime format is ISO 8601 (eg 2025-01-01T00:00:00Z) #"$filter" = "sentDateTime ge 2025-01-01", #"$top" = "100" // I believe max is 1000 - adjust as needed for performance ] ] )Parse the response as JSON (Json.Document). You'll get a JSON record with fields:
- value: list of records to upack with Table.FromRecords
- @odataNextLink: if more records exist than specified in $top, this will give the REST url for next page of size $top
8 Replies
Hi anrm6 you can try this
Step 1: Create a Parameter StartDateParam
Value: 1/1/2025 12:00:00 AM
Step 2: Create a new blank query and pest this code
let Source = Exchange.Contents("[email protected]"), Mail = Source{[Name="Mail"]}[Data], FilteredData = Table.SelectRows(Mail, each [DateTimeReceived] >=StartDateParam), BufferedResult = Table.Buffer(FilteredData) in BufferedResultNote: You need to update your email address
I have tested, its filtering rows based on parameters value and overall loading is faster.
Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!
You may want to look into querying your mail messages via Graph API.
Some caveats:
- I have not used this directly in a while, so just going off memory / quick research / testing with Graph Explorer sample tenant.
- Graph integration with PBI is quite limited. Last I checked, though, you could still connect to personal mail through simple http calls and using PBI oauth. See MS party line on this: https://learn.microsoft.com/en-us/power-query/connecting-to-graph
- For a more enterprise solution, you may want to look into the MS recommended route of Graph Data Connect through Data Factory
Connecting would look something like this:
Web.Contents( "https://graph.microsoft.com/v1.0", [ // can query me/mailFolders and nav via childFolders; eg me/mailFolders/<id>/childFolders/<id>/messages RelativePath = "me/mailFolders/inbox/messages", Headers = [ accept = "application/json" ], Query = [ #"$orderby" = "sentDateTime desc", // props list for select: https://learn.microsoft.com/en-us/graph/api/resources/message#properties #"$select" = "id,sentDateTime,from,subject,bodyPreview", // eg filter to this year; datetime format is ISO 8601 (eg 2025-01-01T00:00:00Z) #"$filter" = "sentDateTime ge 2025-01-01", #"$top" = "100" // I believe max is 1000 - adjust as needed for performance ] ] )Parse the response as JSON (Json.Document). You'll get a JSON record with fields:
- value: list of records to upack with Table.FromRecords
- @odataNextLink: if more records exist than specified in $top, this will give the REST url for next page of size $top
Hi anrm6,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to MarkLaf & Royel for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
Havent been able to figure out a faster way tbh. I am not a technical person so unsure of how the API thing works
Hello anrm6,
The suggestions using your existing StartDateParam and the Graph API are great options, but I understand the API might feel overwhelming. Sticking with Power Query, you can improve performance by archiving older emails into a .PST file via Outlook to reduce your live mailbox size, then reconnecting to it. Alternatively, if you're in Power BI, try setting up incremental refresh with your StartDateParam to load only new data after the initial run. Test these and let me know how it goes.
Best regards,
Ganesh Singamshetty.
Hello anrm6,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
Hello anrm6,
Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.
