Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
Now click on Expand icon on Attachments column header and select AttachmentContent to expand.
Then click on Combine Files icon on AttachmentContent column header.
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.
After that, you will have queries like below. In the Mail query, all data is combined. You can transform data then.
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.
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.
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.
Now click on Expand icon on Attachments column header and select AttachmentContent to expand.
Then click on Combine Files icon on AttachmentContent column header.
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.
After that, you will have queries like below. In the Mail query, all data is combined. You can transform data then.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |