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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors