Live Excel File Based on Outlook Mailbox

[Anonymous]

Hey everyone,

I am facing a challenge automating the dashboard since my reports are not based on a DB such as Oracle, SQL Server, etc..

My  PBI report concentrates on sales from different aspects (trends, forecasting, customers and such).

In the current situation, I receive the data on a monthly base directly to my email, and I have to clean the data, and later on, add it into an aggregated excel file I manage with all of the prior sales.

I thought about opening a destined Outlook account just for the excel files and somehow create a "live" excel file that connects to the outlook and consistently aggregates all of the data in the mailbox without me involving (except for errors and data anomalies which require my interference).

This way I can achieve automation since once a new email arrives, the excel will pull it right away, the excel will be up to date and thus I can save wasteful manual work.

Since I am new to PBI I am unaware of methods to achieve data connectivity. I would be grateful if anyone could assist me with sources or discussion I can use in order to apply this idea.

Thank you all in advance!  

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

You could connect to excel file in outlook with Power bi desktop directly.

For Power BI Desktop, you need to click on "refresh" button to get the lastest data each time, 

After publishing to Power BI Service, you could set schedule refresh(auto refresh on shedule) for it.

For more details, please refer to the following article:

"Using Email Attachments As A Data Source In Power BI"

As for cleaning data, you could use Power query inside Power BI to clean data and use Power pivot to create more organized models and visuals.

Data clean in Excel && Power BI

Clean and transform data in power BI using Query Editor

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for replying,

I will try to explain myself better and I would be really glad if you could validate it is feasible using your idea:

I will get daily/weekly sales reports from my distributor including relevant sales data.

 Each report will have the same data structure, however, it will include only the current day sales. 

What I would like to do is to create an aggregated sales table that will constantly scan all of the emails in the mailbox, collect the files and create a conclusive sales table containing up to date total sales history. 

Given that, I will have a "live" single sales table that will auto-refresh constantly.

@v-juanli-msft  , you suggested connecting to my outlook directly with Power BI, however, is it possible to perform such a procedure only with Power BI and by that give up on creating the external excel file which will extract the data from Outlook?

If so, do you have any source or explanation doing so? 

Thanks for the help so far.

All the best,

OB