Organizing demand forecasts is a crucial task that involves organizing data from multiple sources. The sheer volume of emails and the need for accuracy make this extensive, redirecting crucial time and resources away from higher-priority, strategic initiatives.
Managing demand forecasts is a crucial but tedious task that involves organizing data from multiple sources. Typically, forecasts arrive as email attachments from customers and each file often has different formats, necessitating time-consuming data cleaning and standardization. This process includes opening each email, saving attachments, converting data into a consistent format, and manually inputting it into a master spreadsheet. The sheer volume of emails and the need for accuracy make this a repetitive and error-prone task, diverting valuable time and resources away from more strategic activities.
The extrakt.AI app can be utilized for transforming various documents into excel files and we can automate the process by getting the extractions sent directly to email. To automate the workflow, we can use RPA tools like Power Automate, ensuring that as soon as an email with an attachment is received, the CSV file is automatically saved directly to an EDI upload folder (e.g., SharePoint). You can read about the whole process here. In this blog post we go a step further and will automate the process of entering new data into an Excel table on SharePoint. We'll start by receiving the extracted data from extrakt.AI in Excel format via email. Then, we'll use Power Automate to transfer that data from the email attachment to our master Excel file on SharePoint. You can see the whole process in the video below, but we have also provided a step-by-step guide here, so you don’t have to do them manually, but can simply import them to Power Automate.
a) First, go to Power Automate and, on the left, you will find: + Create button.
b) Select Automated cloud flow and name it: “Copy data to a Master file” (or whatever you want). Flows trigger is SharePoint; “When a file is created or modified (properties only)”.
a) On your SharePoint choose a site address where your excel extraction files are being saved to (learn here how to automate this step).
b) Choose the Library where your excel extraction files are being saved to.
In order to transfer data from one excel document to a table in our master file, we have to make sure the extracted data is in a table as well. Here’s how we can do that:
a) We add an action from Excel Online (Business): “Create table”.
b) Under “Location” choose the SharePoint Site where all your excel extractions are saved to.
c) Under “Document Library” choose the correct Document Library on SharePoint where all your excel extractions are saved to.
d) For a “File”, do not click on the folder icon. Here we need dynamic content, so first click in the text bar, and then on the blue lightning (dynamic content), that will appear to the left or right of the text bar. Search for SharePoint: “Identifier” and select it.
e) “Table Range” is a bit tricky, since we don’t know how many rows our document will have, and each Demand Forecast sent from customers will most likely have different amounts of rows. For this reason, we want to define an excess number of rows in Table Range than what we expect our documents will have. These rows will blank once the table is made, but not to worry, we will eliminate them in a later step. In this case, I know I will have 4 columns and no more than 50 rows, so this is what I will write as the Table range: A1:D50.
f) From advanced parameters choose: “Columns names” and write the names of your columns exactly as they are, separated by comma e.g.: Material ID, Customer ID, Date, Quantity.
Add a "Delay" action. We are doing this to make sure Power Automate will have enough time to create a table within excel file that just arrived on SharePoint, before moving on to the new action. 1 min is enough time for my file, but make sure you also check and customize this time based on your own amount of data. At the end, when we’ll test our flow, if you’ll get a failure at the “Create table” step, you have to increase the delay.
So far, we have received our extracted data in the excel file to SharePoint, put that data in the table and now we have to define the data in our table. The first three steps (a-d) are the same as in step: “3. Create table”.
a) We add an action from Excel Online (Business): “List rows in present table”.
b) Under Location* choose the SharePoint Site where all your excel extractions are saved to.
c) Under Document Library* choose the correct Document Library on SharePoint where all your excel extractions are saved to.
d) For a File*, do not click on the folder icon. Here we need dynamic content, so first click in the text bar, and then on the blue lightning (dynamic content), that will appear to the left or right of the text bar. Search for SharePoint: “Identifier” and select it.
e) For Table* we need dynamic content as well, so first click in the text bar, and then on the blue lightning (dynamic content), that will appear to the left or right of the text bar. Search for Excel: “body/name” and select it.
f) From advanced parameters choose: Filter Query. This is where we filter out the empty rows that we don’t want and have created in step 3. - e) Table range. Here choose any column name and add “ne” for not equals to, then either the word Null or two single quotes. You don’t have to choose all the column names, just one. I choose Quantity, so here is the example on how to write it:
1.) Quantity ne ‘’
or
2.) Quantity ne Null
This way no empty rows will be uploaded to your master file.
a) We want to make sure that data from every new attachment is copied to the master file, so what we need is a control called: “Apply to each”.
b) Click first in the text bar and then on the lightning that will appear to the right of the text bar and search for: “body/value”.
a) Inside the "Apply to each" loop, add a new action Excel Online (Business): “Add a row into a table”.
b) Under Location* choose the SharePoint Site where all your excel extractions are saved to.
c) Under Document Library* choose the correct Document Library on SharePoint where all your excel extractions are saved to.
d) For a File*, click on the folder icon and choose the master file, where you would like your data to be copied to.
e) For Table* we need to select to which table you would like your data to be copied to. In my case this is Table1.
f) From advanced parameters select all your columns. For each column we will have to write this formula/expression: items('Apply_to_each')?['ColumnName'].
This is what it should look like:
items('Apply_to_each')?['Material ID']
items('Apply_to_each')?['Customer ID']
items('Apply_to_each')?['Date']
items('Apply_to_each')?['Quantity']
In the top right corner click save. Now every attachment coming from the noreply@extrakt.AI email will be sent directly to your chosen folder on SharePoint.
Next, let’s create an integration to convert these different types of documents into an EDI-friendly format, in our case a CSV file, which will be sent directly to your email.
a) Go to the extrakt.AI app in the sidebar look for: Create a Template.
b) Scroll down to the sales tab where you will find Demand Forecast Report Template. Add or remove the fields to customize it and once you're done, click Create in the top right corner.
a) Go to the sidebar again and find Integrations. On the top left find a Plus sign and after that click Email icon.
b) Scroll down to find your template and check the box in front of it.
c) Scroll up and go under the Result Email tab, keep Reply to sender box checked.
d) Where you see Email attachment format, choose your preferred format (in our case CSV).
e) I don’t want my email to include the original attachment as well, so I will uncheck Include input with the email box.
f) Once you are done, go to the top right corner and Create Integration.
a) You can find your new Integration under Integrations, where you can copy the unique email address.
b) Forward suppliers email, with the document attached to this unique email address pertaining to the Demand Forecast template.
.png)
Subscribe to our newsletter and we will send AI automation insights like this straight to your inbox on a regular basis.
