Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors