Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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?
Thank you!
Hi,
As mentioned in my previous message, there has to be a Date of Attachment or Date of Mail column there.
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!
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |