Updating data source by replacing Outlook attachment

[Marco3007]

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

[Ashish_Mathur · Accepted solution]

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.

[Marco3007]

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? 

Thank you!

[Ashish_Mathur]

Hi,

As mentioned in my previous message, there has to be a Date of Attachment or Date of Mail column there.

[Marco3007]

After banging my head a little, I found what I was looking for – which is to keep only the latest e-mail: I just had to use the option "Keep Rows" and select 1, so out of the incoming e-mails flow, Power BI will only stick to the last one received.

But thank you very much for your time, Ashish!

[Ashish_Mathur]

You are welcome.