cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irlem
New Member

Power BI data connection to MS Exchange Online logging in with the current user

Dear all,

I have established a data connection from Power BI to MS Exchange Online to load my E-Mails to Excel and create some Excel Pivots to analyze Top Senders, number of emails etc.

 

irlem_2-1659041133082.png

 

While setting up the query I am asked to login to Exchange with my Microsoft Office Account.

 

Opening the Advanced Power Query Editor I can see that the following m code has been generated:

 

irlem_4-1659041576022.png

 

let
Source = Exchange.Contents("firstname.lastname@domain.com"),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Inbox\"))
in
#"Filtered Rows"

If I would like to publish this BI report with other users but they should see their own emails. 

 

According to the Microsoft documentation of Exchange.Contents 'If mailboxAddress is not specified, the default account for the credential will be used (see Exchange.Contents - PowerQuery M | Microsoft Docs). So I have removed my email adress from the 'Source' definition:

 

let
Source = Exchange.Contents(),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Inbox\"))
in
#"Filtered Rows"

 

When I now Refresh the Query the Query Editor is asking to 'Please specify how to connect'. Clicking on Edit Credentials...

 

irlem_2-1659043654118.png

 

... I can pick my Office Account from the oauth2 popup ...

 

irlem_3-1659043814157.png

 

... and it shows me that I am 'currently signed in'. 

 

irlem_4-1659043892974.png

But clicking the 'Connect' button a notification is displayed that 'The credentials provided are invalid'. 

irlem_6-1659044058380.png

 

I would be happy if someone could give me a starting point on how such a query will login to Exchange with the current users Office Account? Or if there is a better way to share predefined email reports with users to analyze their Inbox based on Microsoft tools that can be set up by me as a non developer?

Thanks for any help in advance!

Martin

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

Hello - you can try creating a parameter for the email address, add it inside the Exchange.Contents function and then save the report as a template (pbit).  When the user opens the report they will be prompted to provide the value for the parameter (their email address).

let
Source = Exchange.Contents(paramEmailAddress),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Inbox\"))
in
#"Filtered Rows"

 

View solution in original post

2 REPLIES 2
jennratten
Super User
Super User

Hello - you can try creating a parameter for the email address, add it inside the Exchange.Contents function and then save the report as a template (pbit).  When the user opens the report they will be prompted to provide the value for the parameter (their email address).

let
Source = Exchange.Contents(paramEmailAddress),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Inbox\"))
in
#"Filtered Rows"

 

Many thanks, jennratten. I will implement the 'parameter approach' if I don't find a solution to get the email adress (Office account) from the current user automatically so the user doesn't have to enter it manually. Are there any  other thoughts on how to achieve this?

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors
Top Kudoed Authors