cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frank10
Frequent Visitor

Microsoft exchange - data refresh automatically

Hi all!

 

using Microsoft exchange as source, I create a report in power bi desktop getting data from an email attachment (a scheduled report). The issue is Power BI doesn’t refresh automatically the report after a new email arrival, even i refresh the data.

 

Thanks in advance for any suggestion.

1 ACCEPTED SOLUTION

Hi @Frank10 

 

Yes, you don't need to apply latest filter. Just filter by Subject as the emails have the same name format. Then you can follow the steps below to combine all XLSX files. 

 

For example, I filter by Subject (starting with) and remove other unnecessay columns. After that, I get below table. Click on white space in any cell of Attachments column, you will see its attchment information table displayed below. In each email, there is an XLSX file attached.  

21110401.jpg

 

Now click on Expand icon on Attachments column header and select AttachmentContent to expand. 

21110402.jpg

 

Then click on Combine Files icon on AttachmentContent column header. 

21110403.jpg

 

You will see dialogue windows like "Evaluating Queries..." and "Connecting...". Just be patient and wait for Power Query to detect and connect to files. After a while, you will see Combine Files window. It's similar to when you use Folder connector to combine files in a folder. Select the sheet where data is and click OK. 

21110404.jpg

 

After that, you will have queries like below. In the Mail query, all data is combined. You can transform data then. 

21110405.jpg

 

For more information about combining files, please refer to Combine files overview 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

5 REPLIES 5
v-jingzhang
Community Support
Community Support

Hi @Frank10 

 

Are you using data from an attachment in a specific email? If so, when you refresh data after a new email arrives, it still gets data from the email you have chosen.

 

If you want to always import data from the newest email attachment, you can filter DateTimeReceived column in Power Query Editor to get the newest email (Date/Time Filters > Is Latest). Then connect to its attachment. In this way, when you refresh data, it will always find the newest email attachment to get data. 

21102901.jpg

 

As this is in Import mode, when you are in Power BI Desktop, it will not refresh data automatically. You need to click Refresh to import the newest data. After you publish the report to Power BI Service, you could set up Scheduled Refresh on Dataset Settings page. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

Thanks for supporting!

 

I tried the way you mentioned but I still have doubts.

 

It's right to find the newest email attachment to get data but at the same time I need to check and keep historical data (this means overwrrite only new data). This because starting new year (1st January) the report will restart and if I consider only the last email I will lose last year data. Hope this is clear.

 

You also suggest to publish the report to Power BI Service. As this dataset is confidential I need to be sure to have the full control over data that will not be public or accessible.

 

Cheers

Hi @Frank10 

 

As you need attachments from multiple emails, you can filter rows to keep emails you want to get data from. Are these emails in the same inbox folder? Maybe you can set up some rule at the email box to move all required emails to the same folder, then filter the Folder Path column to only keep emails in this folder. Or maybe these emails have the same name format so you can filter Subject column to find them. The first goal is to keep only the emails you want in the table. Then we can think about some method to combine all attachments at the same time. What is the file format of the attachments? XLSX, CSV or something else?

 

BR,

Jing

Hi @v-jingzhang,

 

thanks for supporting.

 

I already filtered Subject (starting with) and Latest (Date/Time Filters). At this point I get latest email in my table. As said, I need to keep historical data, are you suggesting to not apply latest filter but combining in some way all the email ?

 

Anyway, the emails are in the inbox with same name format and XLSX file enclosed.

 

Thanks !

Hi @Frank10 

 

Yes, you don't need to apply latest filter. Just filter by Subject as the emails have the same name format. Then you can follow the steps below to combine all XLSX files. 

 

For example, I filter by Subject (starting with) and remove other unnecessay columns. After that, I get below table. Click on white space in any cell of Attachments column, you will see its attchment information table displayed below. In each email, there is an XLSX file attached.  

21110401.jpg

 

Now click on Expand icon on Attachments column header and select AttachmentContent to expand. 

21110402.jpg

 

Then click on Combine Files icon on AttachmentContent column header. 

21110403.jpg

 

You will see dialogue windows like "Evaluating Queries..." and "Connecting...". Just be patient and wait for Power Query to detect and connect to files. After a while, you will see Combine Files window. It's similar to when you use Folder connector to combine files in a folder. Select the sheet where data is and click OK. 

21110404.jpg

 

After that, you will have queries like below. In the Mail query, all data is combined. You can transform data then. 

21110405.jpg

 

For more information about combining files, please refer to Combine files overview 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!