Our client today is a burger company. They want our help to do some invoice processing. That is, they received emails with invoices as attachments, and they want our help to extract the data automatically out of those attachments and get them into Excel. It could be a database too, but for simplicity reasons, we choose Excel. Let's see how it's done.
So we want some input data. That is the data that's going in through emails. Here I saved them at my desktop in a folder and they look like this.
I highly recommend you to download these six invoices to your own disk and do the operations with me. That is download the data. They are in the description below and then open up Power Automate.
In that way you will learn the most. So this one is invoice number one. It's quite standard.
We have an invoice number up here. We have a date, we have some data, we have some addresses, we have some total, taxes, whatever. And then we have a table here. That is, we only have one row in our table and then some headers, but we could have 10 rows in our table.
And let me show you the second invoice. So I fold it. So the second invoice looks exactly like invoice number one, apart that we just have two rows here. So we need five invoices to train our AI model.
And we have six here. That is, we will use the first five ones to train our model. And then we will use the last one to test our model, our flow, whenever we are done.
Let me stress that today we are working with structured documents. That is, that our documents, they look the same. They are in the same format.
We can see that they look like each other. That is structured data. We could also work with unstructured data.
Then we will have a bit different process. I'll show you this one in the coming weeks, so be sure to subscribe and get notified when I make a new video. In that way, you will not miss the coming cool Power Automate AI unstructured video. That was a long word, but let's solve it for this today. It doesn't have to be invoices.
Today, we will use invoices as sample dataset. It should just be structured data. That's it.
So now we inspected our data. Let's create an AI model and let's use that model in a Power Automate flow. So let me close down these two here. And I think I opened another folder. So the first thing we do, we go to AI Builder and then we click Build.
That's it. So here I need to do some form processing that is structured data. So I click here.
We could name our AI model something. I will just call mine invoice processing like this. Then I click create.
It's very, very easy to use the AI Builder in Microsoft Power Automate. We need to specify what information we want to extract out of our document. And that was the invoice number, the total.
the items in the table and as well so we can click add first we have a field that is these fields that could be invoice number total whatever then we have a single page and as you can see that could be our table a single page table that could be a table with our our items in. We just have to specify if our table date spans one page or multiple pages. Ours is only a single page, so we will choose this one afterwards.
Finally, we have some checkboxes as you can see here. But let's specify our fields. So I click Fields. I click Next. The first one that was the invoice number.
I click Done. Then I add another one. I want another field, so click Add.
Next. The next one that was the date and click Done. And I'll add our third and final field like this. That was the total. You're very welcome to take more data out of your invoice.
But for now, this one is sufficient as the intuition will be the exact same thing. Now we just need our table with data in it. And again, it doesn't have to be an invoice. It could be whatever document you have with a table in, either PDF or a PNG or JPEG. So click single page table and then click next.
We can call our table something that doesn't really matter that much, but I can just call it burger items. Then I need to tell what name my columns should have. So if I click the column one, rename column here, and I would give them the same names as they have on my invoice. So that is item. Then I had quantity.
If you're in doubt, simply just open the burger invoices here and see that what I'm doing is I'm doing the right thing. Then I'll have the rate and finally I'll have the amount. So now I added headers for my table and I can click done.
Now we can click next and we need to specify our input data. So that is called a collection. So if I click new collection here and then I click the plus sign, I click add documents. We will wait a few seconds and a data source will pop up. A data source that could be a SharePoint or it could be Azure Blobstore.
I haven't tried with that one so I cannot tell you how to do it here. It will be the exact same things. But as you have downloaded the invoices to your desktop most likely, choose this one.
And this one is training data so almost always will happen locally. But you could have it online. Here Press the first invoice, then hold down shift and take number five. Now we have chosen 12345. Don't choose number six. Then I click open.
So here I click upload the five documents. In a few seconds, my invoices, that is the sample data set, they are uploaded and we will use these five invoices to train our AI model. And I click close.
Now we have a collection. You can rename this as well, but it's not really that important. Keep in mind that we could add another collection for another type of invoice.
Say we only have two invoices in, then this AI builder could automatically sort which invoice that was. You could also create 100 different collections, but that would be quite a mess. And that would be this unstructured data.
We will use a model for that. But for now, we will only have one collection as our invoice always looked the same. And I click next. So now we need to tag. our documents and that is we need to specify where our data is.
So first one you can see here we have five documents up here. We can scroll through. I'll take the first one. First thing I do, I need to take this little cross here.
So move it over here, hold down your left mouse button and make a selection. Then we need to say what field is that? Well, that is the invoice number. So pick that. You can see here, turn screen.
Now I have the date and I will intentionally make an error. So let's say that I have chosen this date here. That is the due date. But I wanted to have this one up here so I can just click escape and we're done. But say that I went one step further, that if I took this one and I even took the date, now you can see it turns.
screen simply just click the three black dots here and remove tag let's choose the right date that will be this one here I choose the date then we want a total like this total now we want to extract a table this is an invoice table but it could be any table you wanted to extract so what I want to do here is that I mark my row again by left holding down the left mouse button and I mark it that is the burger items and then you can see here we can We have no more rows, but if we had, you can see here there's a ruler here. Simply just left click and that one will separate our rows. But for now, we will take the columns and to do so, press control in.
And you can see we can move around. So control here, control there, and control there. Now we have four columns. We need to specify our headers.
So this one is item. This one is quantity. This one was rate. And then the amount.
If this goes too fast, simply just... just pause the video or rewind a bit, it's designed for that. That's it. And you can even see here we have our data up here in a table so we can inspect that what's going on is the right thing. Then I click Done.
That was number one. Then I take number two. And we do the same thing here. I'll do it a bit faster. Nothing has changed.
So you should just do the same as we did with the first one. And I take the total. I take the table here. That's burger items.
Control, like this, specify the column headers. So here quantity, the grade, the amount, click Done. On to the next document up here. That is number 3. That's invoice number, state.
that is total and again this feels a little bit intuitive as we are automating things and then we need to do these things manually but that's just how it is now we get a little challenge but let's first add the columns so there's one column here one column here and one column there now we just need to separate our rows so go in the middle here and left click that's it we have our rows and columns we just need to rename the column so i click here item quantity then we You have the rate and the amount. That's it. Then we click done. Let's take the fourth one. We take the invoice number here.
We take the date. We take the total and we take the ID. from here. That one would be the burger items. You can see it automatically detected it.
Now we don't have to do it. I just click done. Now we have the last document. So that one would be invoice number, date, total.
Again, if you have questions, you're more than welcome to put them below in the comments. The section is designed for that. I will make sure I will get back to each one of you.
Maybe not the fastest. So here I just click next. That's it.
Now there's a resume of our what we have done. Then I can train my AI model. My model is training. Go to models. Here it will take a few seconds to train.
It will show up here eventually in a few seconds and it will train. It will probably be taking around one minute. We won't sit around and waiting.
one is training we will go to power automate we actually in power automate but we'll go to create a flow that we can import this one into so go to create we will need to choose a flow and our burger company they get the invoices in by email so we will choose an automated cloud flow that is this one here we will add a name i can just call this invoice processing like this then i want to search a trigger and this is when a new email arrives so that is this one choose the one that corresponds to your outlook mine is office 365 yours are probably the same so choose this one then we click create so we have a trigger. That is whenever we get an email and we want to make it a bit more strict, it needs to have an attachment and say that we always have invoice in the subject, we can specify that as well. So we won't have to process all emails. The first one, take only the emails with attachments. Yeah, that is quite important.
It's not important, but it will not make the flow run every time we get an email. So now it's only with attachments. We want to include the attachment in the flow as we will use them.
Then we want some subjects. So say that it always says invoice in the subject. We simply just specify the filter here.
We can specify more strict filters, but this one is sufficient for now. So I can collapse the advanced options. So we can do that.
By the way, I always recommend you to look what's going on here in the advanced options. Sometimes you will use it. Then we want to have a loop that loops each attachment that should be in this email.
Imagine that we could have more attachment to the same email. So I click new step. Then I want to find and apply to each here and I click it. So I want to take each one of these attachments from up here.
So I click in here and I go to the dynamic content. and scroll a little bit down. Well, either search for attachments or find this one here.
There are a lot of dynamic content that is named something with attachments. Make sure you pick the right one. That is this one. So this one is iterating through each attachments. Now we can start to process them.
But first we need to pick a place where we want to save our data to. That is the data we get out of the invoices. We're saving it to Excel and to do so, we will create an Excel book in Excel.
OneDrive. So I go to my Microsoft Office and open up OneDrive. Here I can just create it in my main directory in the root of it but feel free to do it in the subfolder as well. You can also create this in Excel online but for now this is sufficient.
So I click new here and then I take Excel workbook. So that's it. Let me enlarge it a bit so you can see what's going on. Here I have just a plain Excel sheet.
I can choose to rename it Right now it's just named Book. That's fine for now. So let's create a simple table with just four column headers, and that's it. The first one that should be the invoice numbers. This one is just the data that we want to save.
Then we have the date. We have the total. And then we have our items. So that one was the one from the table. First, we will save it to the same Excel cell.
That will look a bit messy, but it will be fine. We will have to use some data. data science on it to get it more beauty. And we will do so.
So now we have our table here. We have nothing in it. That's fine. So mark the four ones.
here then click format as a table choose whatever format you want that doesn't matter at all so I just pick one random specify that your table has headers as it does these ones are exact headers we don't have any data in it but we will add it so now we have our table. To give it a nice name, it's not necessary, but it's always best practice. So click inside anywhere in it, click Table Design, and we have our name up here. So here I can say Invoice Data. This one is just a referral so when we use it in Power Automate we can easily identify that is the right table it's not very important for now again click in it table design you will see it up here but it is important when we work with say 10 different tables that they are not named table 1 table 2 and sometimes they even name the same if you use different books so for now this one is good so we can go back to Power Automate then we go to AI Builder and models and we can see that hopefully our model has been trained let's just click it because we need to publish it we can also run a test on it so let's do that first we run a test and then we want some sort of an image and we have that because that was invoice number six so let's test that it just worked the way we want so i put in number six you can see here that we have five rows now in our table so it will analyze and then it will see if it can find data that we specified it looks like it it has this invoice number six confidence score 99 that's very safe We have the date, we have the total.
And to inspect this table, simply just left click and that looks about right as well. So now we have tested it, we can close it and we can publish our model. So our model will get published now so we can use it in Power Automate and Power Apps. So let's just stay here a bit.
And while we wait for this, if this video helps you, you can really help me a lot if you give the video a thumbs up. You can also subscribe to my channel. I choose to use it here, but since we are in the middle of a flow, we will just find the flow again. So I check the invoice processing.
I click edit. Open up the apply to each in a few seconds like this. Click add an action. Now we want to use our AI model.
So extract information from forms. pick that one so we want an ai model and that one we call invoice processing so simply just click the drop down and choose it here the form type we need to specify what form type it is well here it is pdfs but as you can see you can use jpegs or pngs so we choose the pdf then we will specify a form to process and that one will be the attachments from up here so i click in here then i go a little bit down now you can see that it's quite clever power automated automatically choose the autumn old attachments content. If you want, you can see more, but there's no reason to it. We will take the attachments content again, choose the advanced options. That is only pages.
We will just choose the entire page as those ones are just one pages. That's it. So now we have extracted it.
We want to add them to Excel. So take a add row into a table in the add an action like this. So add row.
into a table here like this then choose a location mine is on OneDrive for business the document library that is OneDrive now I just need to find the file so pick this one here and when we were in the root of OneDrive, my book is here, but if you have any subfolders, just click your way into those subfolders. Mine is here, that is the book. And now we will select our table, and that's why we gave it a name. You can see here, invoice data. Here, this one opens, that is the advanced options.
Now we need to specify what should go inside each of the four column headers. The invoice numbers. Well, pick the dynamic content. And as you can see here now, this form extractor, that one gives us some dynamic information. And you should look for the value here, because those ones are the value.
So if I go a little bit down, invoice number, value. This one was our defined string, invoice number and the value. that is the value what went in there so then I pick the date again choose the date value you can also search for dynamic content up here but for now it's okay and pick the total that one was here and then we have our items so here we can see the individual column headers item rate amount and quantity but we want to extract everything because we will extract the whole table that one will be our entry in excel a bit later we will see how to extract each one in separate places but here we will choose the burger items entries that's it that was our flow really simple flow almost just drag and drop so I click Save let's try to run it because it's a trigger based flow I need to send an email to myself to see it will work so So I go to Outlook and I click new message.
Let's send me myself an email and I will be sure to in subject put in invoice. Now I just need to drag in invoice number six. I can drag in whatever I want.
It will work. But that's it. Then I click send in a few seconds. It will show up in my inbox and hopefully that one was it. And as you can see over here, our flow will run in a few seconds without errors.
Usually there's probably a 15, 20 second delay, but that's fine. This one will work in the cloud. We will not see this flow run in in real life.
That's it. Now your flow is running. It's processing the document and in a few seconds this one will have a green check mark as well. And that's it. We can inspect our data.
So if I go to OneDrive and then the book we have here, we have our data here. So if I just expand these ones here, we will see that we have we have invoice number, we have date, we have total and then we have some a bit of a messy data. But that's fine.
And another thing you can see that this one has has automatically formatted it for my local currency which is Corona. To fix this you need to just right click in your data set. There's a few ways to fix it but we will fix it in the easiest way. So right click, number format, currency.
Make sure that your currency is now in US dollar. So if I say dollar, English, United States, that's it. So and for the next entries it will look like this.
Now well this one was was not really pretty and see that we have actually our data here, but a lot of irrelevant data for our row that was not really nice. So let's try to fix it. If I go over to run history, we need to inspect the log before we do anything. And if I inspect my data, let me scroll all the way down to our items, mark everything and then copy it. We go to this JSON viewer.
This is just a JSON. It's just a format that the data is in to. transfer them efficiently. So if I paste them in here it still looks a bit messy.
But if I click beautify it looks a bit more nice. You can see that we actually have some data that we want to use. We have the item here.
We have the bunds. That one is the value of this one here. Then we have some key value pairs.
So if I say here I have the bunds then we have the rate and the rate was 0.25. 29 and so on. Each one of these entries in our table, if I click up here, that was one item.
You can see that we now have 160 lines less. I can click here, here. and here so we have four rows that one corresponded to the four rows in invoice six we want to make it a bit more beautiful so to do so we will use a bit of a bit of magic but it will not be that it will not be very very hard to solve we will just go inside this JSON to get the item and then we will have the rate we will have the amount and then we will have the quantity. So let's create it in Power Automate so we can have one entry for each line in the table.
So I go back here, I click edit and instead of this our items click here and now we will write our expression. So I'll go to expression and let's just talk about what we want out. So if I go to my JSON here, I want for each entry, I want the item out, I want rate out.
I want the amount out and then I want the quantity out. And since, let me just do this, and here we can see that we have one entry. So it simply just go in, we already in the entries, I just want item, rate, amount and quantity. And I want to vet the corresponding value out. So I take the item, and then I want the value that was bunched.
To do so it's very easy. So go to the expression. The first one we will take that is the we will take the items from this apply to each and this is a very good approach to learn to white function so I'll say items like this then I'll say parentheses single quotation marks and I'll say apply Apply to each.
Whenever we are referring to what comes out here and whenever there's space, we are doing an underscore. And since it's applied to each two, I'll take another underscore and then two. And now we are in the entry. So now we can work our way down in the tree.
So if I say question mark and first thing that I wanted, that was the. That was go down to item, then value. So I'll take the value of this item and the same thing I'll do with the rate.
If we see here, rate value, I'll take that one. So if I go back, those ones are written in hard brackets. So I'll say hard brackets, single quotation marks, item.
And then we will have the question mark. We are going one down in the tree, hard brackets, and then we want the value. Just see if I go up.
So I have the value here. So then I just say in single quotation marks value. So this one is sufficient.
This one takes the value of the item of this, the first entry. So if I click OK, it says that it's wrong. It's not. That one is it.
So instead of just writing out the three other ones, they have the same structure. I can just click here now. Let me copy all this one here. And then go back. We just needed to copy it.
So go here and go here and make a space. And now we can make the other expression. They will look all for expression will look the same. So if I go to expression, I can paste in this one here. That was what was the one from item.
And then just change item to rate amount and quantity. That was very easy. So it's basically the same approach. So what was the first one? That was rate and amount and quantity.
If I want to have it the same way as in my table, then we have quantity before, but it's really just a matter of Excel Gymnastic. So now I have the quantity here and the same approach will go. I'll pick a space, then I'll choose expression, paste in this one here. Then I want rate like this.
Click OK. It's invalid. It's not. Sometimes it just happens and I click another space. Click Expression, paste it in again, and this last one, that was the amount.
If I misspell this one, it will give an error, but we can easily fix it. So now we have created our flow and we can take each line in the table of our attachment for invoice. So I click Save here.
Let's test it. And I can process each one of the rows to our database which is Excel in this place. So now I save and test it. I'll try to send an email to myself with an invoice in. So I go to my inbox, click new message, say Anders Jensen, pick the six here and I click send.
So I need to put in a subject and that was one of our invoice. Otherwise this won't pick up. That was a filter we made in the beginning just so you know what's going on.
So now I've sent it. We got the invoice here. We can go to our flow.
Flow will run in a few seconds and then we will hopefully have four lines of new data as it will take each row of the invoice table as an entry in our Excel sheet. Again, rewind the video if you think this went a little bit too fast. This is not easy stuff. However, you can easily do it.
Just rewind the video and replicate what I did here. It will become very easy to you. When you do so, our flow ran successfully.
And if we go to OneDrive again, there you go. You can see that we now have, of course, the four, the invoice number, the date and total, they will be the same here. But now we actually got our nice data out. We could also, of course, also have assigned these ones to separate columns.
But for now, that was fine. Take my full Power Automate course by clicking the playlist in the middle. That one will teach you everything about Power Automate.