Automate Reading of MSExchange Email Attachments to Microsoft Fabric Lakehouse

Hello everyone this is Abiela David in this video I'm going to show us

how to automate the reading of Microsoft Exchange emails attack files into the

leghouse object of the synapse data engineering platform of Microsoft Fabric the data I'm going to read span from sales report 2013 to sales report 2016

Now these are the data in my exchange outlook. We can see 2013 to 2016

After we're able to successfully with data into the lake house, we're going to write a single query in the SQL endpoint of the lake house

And then I'm going to swipe to my Gmail address, send the sales report for the 2017 into this email

And then I'm going to go to the workspace we're going to create, refresh the data in the data

flow gen tool which is power query online go back finally to the SQL endpoint and execute the

code again and by so we should see the automation taking place in other roles the report for the

2017 would be included in the report so without much talking let's get started now this is the

Microsoft fabric welcome page so i'm going to go to my outlook again now these are the reports just

like I mentioned, each of the emails do contain an Excel book attachments

Now let's just see the content of the report 2016.xelSX. So these are all the columns and

of course we want to aggregate all the data across 2013 to 2016. So I'm going to close this

and come back to the fabric. Now the first thing I want to do is to create a workspace. So

I can click on the PowerBeer experience and then click on Workspaces and choose new workspace

Now I'm going to give this as data from exchange and I can give any name for your workspace

Click on Apply. All right so we can see the workspace is created

Now I'm going to switch to the data engineering experience of the Microsoft Fabric and click on Lakehouse

So I'm going to call this one sales repulse and click create

So we can go ahead and connect to the data source. So to do that, I can click on this Get Data and choose new Dataflow Gen 2

And that's going to open the Power Query Online where we can connect using different connectors

All right, so this is Dataflow 1 and this Power Quarry Online, which is quite amazing

Now it's a good practice to change the default Dataflow 1 to something meaningful

So I'm going to call this one sales data to be consolidated and click Enter to make the changes permanent

Now I'm going to click on get data and choose blank query because we're going to use the exchange dot content and function to access the content of my email

So go ahead and click on the next. So what I'm going to do for us is to remain this query

I'm going to call this yearly sales report and click enter. Now in the formula bar, I'm going to get rid of this and call the exchange dot content

M code open the bracket now I need to provide my email address with access the emails from

attack me from now this is my email address so you can see abeola at Exceljet consult

come on microsoft.com so I'm going to come here all right so this is my email address

now you're going to see credentials are required to connect to the exchange source so just

go ahead and click on configure connection okay so we're going to see

connect to data source now you'll see your exchange and the email address now we're going to connect

to our organizational account that's going to be the authentication kind so just go ahead and click on

connect amazing so we can see the different components of the exchange email now the first

i'm going to do is to filter out the calendar people task and meeting requests so click on the

filter or check all of them check mail and click okay Alright amazing now when I click on this I can see the different attributes the

columns in the table now I'm going to click on this expandable icon now we do

know need all of the columns we only need the photo path subject sender and I going to scroll down and I going to choose as attachments Now in other words when the e do contain attachments

And of course, I'm going to click on attachment itself, so go ahead and click on okay

Now we can see all the columns. I'm going to get rid of this first

I don't need this again and remove the columns after the right click

Now the first thing I want to do is let's just check the particular

Particulars of the sender now the email is actually coming from Abiladavid which is the name and the address is this

Yau email address now what I'm going to do is to click on this expandable icon because we want to see the names and

include some constraints or some filter on the address column so make sure this is checked and make sure if this is check automatically by any reason

uncheck this okay click okay okay so what I'm going to do is to only

check the email address that equal to abela.david 0.1 at yahoo.com so click on the filter drop down and uncheck all of them. I'm going to check the only one we need click okay. So that's a way of including the constraints or the rule to only return

folder that comes from or emails that comes from abela david. So I haven't done that now you can see in the photo path

now it can actually point not only to the inbox but including deleted items now in other words

if we have this kind of report deleted it's going to be included if we do not apply filter to the

photo path so i'm going to uncheck click on this filter button unchecked deleted items so it's

going to be looking into the inbox only go ahead and click okay amazing so we can see the

photo path is points to the inbox now for for the as attachments

Now we're going to set another rule by ensuring that only field that do contain attachments

are included in our data integration. So I'm going to choose equal to, now I'm going to choose drop down, yes, true rather

So in the case where we have false, it's going to be knocked off, so make sure it is checked

to true. And that's lovely. Now we can even go ahead and specify

keyword so I'm going to just text filters and I can say that contains now for the emails

now it must contain sales report keyword so I'm going to type in sales space report

and click on okay okay so I've been done all that I'm going to grab all these columns

hold down the shift key to the as attachment right click and choose remove columns

because we don't need them again all right now when I click on this I can see the different the different different different kind, I can see the different kind of the different kind of the different kind of the different kind of attributes

, I can see the different kind of attributes , the different kind of attributes, the different kind of attributes, such as the different kind of attributes

such as the name of the file, the file, extension, is in line, content, type, last

and so on and so on and so forth. Now I'm going to check this box, and we only need the names

name, extension, and the attachment, content itself. So we really check that three columns

Go ahead and click okay. We can see the data is now in binary file

binary file so this contains 2013 14 15 that's love now we can even further filter down

the extension to only pick dot excel s x so just text filters and that contains or we can

see that equals to and i'm going to choose dot xls x from the drop down click okay

do not need the two columns i'm going to pull down the shift key and click on the name

right click and then remove columns amazing so the last I want to do is to convert this

binary file into Excel workbook and of course promote the errors now I'm going to come

to the formula bar and use what's called table dot transform columns and functions

so I'm going to type that here carefully transform columns open the bracket and make sure the

the name is accurately typed table. Transform columns. I'm going to call you and put in a comment

I want to access the attachment content. So I'm going to open a square bracket and inside double code

I'm going to point to the attachments content. Make sure it is typed the way we have it here

And I going to come here put in a comment So I going to use the H Now the H we actually convert each of the binary file into Excel work books Now we going to come here put in the comments So I going to actually use the H So I going to actually use the H Now the H we actually going to convert each of the binary file into Excel Workbook Now we going to use the Excel

Workbook function in the bracket. Now, for the first argument, we're not going to point to the binary

So we're going to use the underscore, which points to the binary files

And they want to promote at the same time the headers. So I'm going to come outside here and close of the table

transform columns and click on this to commit and then the banner files will be converted into

excel standard workbooks so you can see we now have tables when i click on this i can see the

name the data itself the iron kind and hidden now i'm going to click on this expandable button now we are

interested in i'm going to uncheck all of them name and data now the other attribute not

it necessary so i'm going to click on okay okay and finally we're going to click on this expandable icon

and then we can see all the columns so the other date customer name city account manager

product price unit and sales amount now again if this is checked for any resume just

original column name as prefix please uncheck that go ahead and click okay amazing so we can see we have

the data combined and that's lovely. I'm going to right click on this name and remove columns

And the last thing I want to do is to assign appropriate data types. Now for this is

this edit value, I'm going to choose date data type. And then for the text, I'm going to grab

the customer name, hold down the shift key, click on the product column right click and choose

change type. I'm going to choose text. So we're going to change that to text data time

and that's lovely and let's put to the right for the columns such as the price and

sales amount I'm going to hold down the control key and click on the sales amount

right click and then change type want to choose currency data type and for the

units we're going to use the old number data type so I'm going to click on this icon

and choose whole number and those are the amazing transformative task that we're going to perform

in this exercise now the beautiful part of the power collier online is that we can see all the

steps in diagrams i'm going to collapse the data preview and this is the amazing steps that we've

been going through that's quite lovely now let's go to the tata preview and expand or maximize

now we are done with the transformative work we've renamed the query so in the past we're going

to see the ability to choose the destination here but that has been changed

in the lake hours so we can see so many changes coming up even as time goes on so the

destination is automatically pinpointed to leak out so we don't need to check that

or choose that again so I can go ahead and choose publish now or later let's go ahead

and publish now I can see the data flow staging lake outs the

datasets and for you can see the SQL endpoint and for the warehouse we can see the

default data set the warehouse and of course we can see the sales data to be

consolidated which is the data flow jet tool in other words the power query so if

there's a need to go back to the power query you're going to click on this particular

name and of course you can see this icon this icon tells you straight away that

this is power query online now let's go ahead and see the data in the SQL

endpoint where you can begin to write query against the data So click on the sales report

Amazing. So we can see the name of the schema. We can see the name of the database owner and the name of the table is

sales, jelly sales report. And then we can go ahead and of course we can even see

the data set which is quite amazing. Now let's just write the SQL query just like

you know we said. I'm going to click on new SQL query and that's going to

open the canvas for us to write. our query now let's just do select star from let's see all the content frost so I'm

going to grab this which is allowed and go ahead and click on run and that's going to

execute and return all the queries now the thing you want to do I'm going to just click

down I just move this down so that I can see what is going on now we actually want to aggregate or want to see the total sales amount generated for each of the year so we going to see from 2013 14 15 and 16 so I can say

select we use the year function to extract from the other dates I'm going to

call the other date underscore order underscore date close the bracket and I'm going to put

in a comment now I'm going to use this some aggregate function and let's check the name of the sales amount column so sales underscore amount okay

and I'm going to call it you know total sales amount click enter now I'm going to

specify that it's coming from now I can grab this coming from the yearly sales

report so I want to group by this year that we're going to extract I can control

and country v now let's go ahead and order by now let's order by the total sales amount

and let's do descending now that's fine let's go ahead and put it so I'm going to grab this

query and click on run amazing so we can see the report actually the name of the file the

name of the file in the sales report is actually 2012 so let's see it's really not 2013 so it is

2012 so I'm going to come back here so we can see report for 2012 we can see for

2015 2014 and 2016 which is amazing now I'm going to come to my Yahoo and this

is the report sales report for 2017 now I'm going to go ahead and click on send I'm

going to send it to my personal even address the abula at Exceljet

Consor triple one on Microsoft.com and go ahead and click on send

and let's check the exchange so let's just refresh which we see it's amazing so you

can see it just dropped in that's wonderful now you can see for now we do not

have 2017 included in this report okay we can even just okay let's just go ahead

so what I'm gonna do is I'm gonna come back to the workspace we created and just

like I said this is the power query and we can go ahead and click on refresh

now and then we're going to wait for the old step to incorporate the newly

received sales report 2017 now that can take some time depending on how

voluminous the data is like and just you know click on this icon to monitor

the refresh history so we're going to wait for some fee you can say it's going

in progress this status is in progress so you're just going to

wait for some field in a moment and then we should see succeeded so we're going to

wait and come back to this video amazing so we can see succeeded now it's

now being included we can go ahead and close this and click on that again and

then let's see the query that we just execute TED now I'm gonna scroll down

here I'm gonna scroll down and there I can see the query under the

my queries now that's actually story automatically which is quite amazing I'm

going to grab the query again and click on run and then we should be able to see

2017 sales report included now this is quite amazing because we are able to

include the data by sending to the exchange and then we refresh the whole

process in the power query online and then we execute the query again and that's

it we have 20 2017 report so the total sales for 2017 is 22,462 and I'm going to come to the email

a check I'm going to click on this and let's grab the sales amount column

control shift down our key now in the status bar we can see we have 23,000 four

six two which is quite amazing so this is basically how we can use or connect to data

in our exchange email get it on the power and into the lake house write SQL against that data sets and then include a new

data refresh the whole thing and then we see the data then included in the final

path so I hope you enjoyed this video if you do share with the friends comment

and give the thumbs up thank you and bye for now cheers