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
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

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.