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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.