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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Kudoed Authors