Forum Discussion
Updating data source by replacing Outlook attachment
Hi! This may be an amateur's question (which I am, have started recently...), but for me it is tricky:
I have a scheduled report sent to me by e-mail, from which I only need the Excel attachment.
I managed to connect Power BI to Exchange Online and I can point to the specific folder which the e-mail containing the attachment is archived in with an Outlook rule. I can also extract the information from the attachment in Power Query, but here what's killing me:
- I need to make visuals and reports with the information in these attachments;
- I only need the latest information, i.e. only the last attachments that come in the e-mails. As I use the data from one Excel file, it will remain until the next day, but once the next arrives, I will not need the previous ones any longer;
- I managed to get the data from all the piled up e-mails already in the folder in my Inbox, but again, I had a hard time using only the data of the current day and disregard the other ones in the visuals...
Any easy way out that I have not noticed? If DAX or M is needed, I am not very good at them but I can give it a try!
Muito obrigado / Best Regards,
Marco
Hi,
In your dataset, I am assuming that there is a column which represents "Date of Attachment" or Date of Mail". In the Query Editor, write this "M" language formula to get today's date in a column
=DateTime.Date(DateTime.LocalNow())
In "M" itself, write this formula to check for whether the Date of Attachment is the same as Today's date
=[Date of Attachment]=[Today]
Today is the name of column with Today's date. Filter this new column on TRUE.
Hope this helps.
5 Replies
Hi,
In your dataset, I am assuming that there is a column which represents "Date of Attachment" or Date of Mail". In the Query Editor, write this "M" language formula to get today's date in a column
=DateTime.Date(DateTime.LocalNow())
In "M" itself, write this formula to check for whether the Date of Attachment is the same as Today's date
=[Date of Attachment]=[Today]
Today is the name of column with Today's date. Filter this new column on TRUE.
Hope this helps.
Hi, Ashish! Thanks for replying!
What I did: I connected to Exchange Online, then filtered to only pull data from a specific folder in my inbox, and then I removed all the columns that are not the "Attachments". At the Fx field I can see this:
= Table.SelectColumns(#"Filtered Rows",{"Attachments"})And below is what I have:
Each one corresponds to an Excel spreadsheet, and I can hit the Expand arrows to get the data itself, but it will merge all the files and I only need the latest data.
Sorry for being so newbie, but where can I move from here? :smileylol:
Thank you!
Hi,
As mentioned in my previous message, there has to be a Date of Attachment or Date of Mail column there.
