Exercise - Build a Power Automate for desktop subflow to write notes into Excel
In this exercise, you create a process within Power Automate for desktop that writes the values of variables that you created earlier into a Microsoft Excel file. Then, you use GPT from Azure OpenAI Service to automatically generate a summary of the invoice to also write into the Microsoft Excel file.
Go to Power Automate and make sure that you select the correct environment.
Select Solutions and then open the Invoice processing solution.
Open the Enter an invoice desktop flow.
Select Edit from the tool bar at the top of the page.
Select Launch app within the dialog.
After Power Automate launches, select the Subflows dropdown menu in the upper left part of the screen. Then, select + New subflow to create a subflow for Enter an invoice.
Name the subflow as
Write_notes_into_exceland then select Save.Note
Subflow names can't have spaces.
From the Actions pane to the left of the screen, expand the Excel menu and then double-click the Launch Excel action.
In the Launch Excel dialog, from the Launch Excel dropdown menu, select and open the following document. Then, select the file icon within the Document path field.
Find and select the Contoso Coffee Shop Invoices Excel file, which is located in the Lab #8 excel files to use in Power Automate file of the AutomationIAD-Learn-student-files folder for this module. After you locate and select the Excel file, select Open.
Note
The spreadsheet for this exercise doesn't contain a password. However, if it did, you could expand the Advanced section in this dialog and then provide a password.
In the Launch Excel dialog, select Save.
From the Actions pane to the left of the screen, under the Excel expansion, double-click the Get first free column/row from Excel worksheet action.
Use the default settings for the Get first free column/row from Excel worksheet action and then select Save. This action retrieves the number of the first free row and the first free column and then stores them into variables.
Make sure that you have the Get first free column/row from Excel worksheet action that you added, and then from the Actions pane, under the Excel expansion, double-click the Write to Excel worksheet action.
In the Write to Excel worksheet dialog, in the Value to write field, select the variable {X} icon. Then, double-click the InvoiceID variable under the Input/output section.
In the Column field, enter the letter
A. In the Row field, select the variable {X} icon and then double-click FirstFreeRow from the Flow variables section. Select Save in the lower part of the dialog.
Use the previous process to add three more Write to Excel worksheet actions to the design space by using the values and information in the following table to fill in the fields.
Value to write Column Row %Account% B %FirstFreeRow% %Contact% C %FirstFreeRow% %Amount% D %FirstFreeRow% When you complete the preceding steps, your subflow for writing notes into Excel should resemble the following figure. You should have six actions within the design space list.
From the upper toolbar, select the Save button and then wait for the subflow to be saved.
Select the Main flow by selecting the Main tab in the upper part of the page.
In the Main flow, from the Actions pane, under the Flow control expansion, drag and drop the Run subflow action to below the last step in the design space pane.
In the Run subflow dialog, in the Run subflow field, select Write_notes_into_excel from the dropdown menu and then select Save.
From the upper toolbar, select Save and then wait for the flow to be saved.
You can now run your flow by selecting the Run icon from the toolbar.
After the automation runs, check the Excel file to make sure that the entry displayed in the following screenshot is added.
Note
A different invoice ID might display in your version.
Task: Use AI Builder within Power Automate (optional)
Note
In order to complete this section of the exercise, you must have the correct license that allows you to have the AI Builder capabilities. In order to check if you have the needed license to use this capability, select the Settings icon at the top of the screen for the Power Automate web browser. Then, select View My Licenses. Here you can see the list of license and capabilities that you currently have and are available for you to use.
To begin this task, start by having the Power Automate Desktop app open to the Enter an Invoice flow. Ensure that you're currently viewing the Main sub-flow.
From the Actions pane to the left of the screen, expand the AI Builder menu and then double-click the Create text with GPT on Azure OpenAI Service action.
In the Create text with GPT on Azure OpenAI Service dialog, select Create Instructions.
Select Summarize text from the list of options.
Review the auto-generated instructions in the Describe the text the model should create area. Remove the following parts of the prompt: "without adding new information. If the text below has less than a few words or looks like a placeholder text, respond "Sorry, I can't summarize," otherwise respond with the summary"
Select the Use instructions in flow button located at the bottom of the dialog.
Update the Instructions by replacing "#Include your text here" with the Input variables created for the flow. To add the variables, select the {x} icon in the right corner of the instructions area, then select the variables from the drop-down list. Remove any unnecessary spacing within the text.
The instructions should now read:
Summarize the text below in fewer than two paragraphs. [Start of text] %Account% %Contact% %Amount% [End of text]
Select Save to add the action to the Main sub-flow. Ensure that the new action is positioned below the Set variable action and above the Run subflow action within the sub-flow.
From the Actions pane to the left of the screen, search for the Display message action and drag it below Create text with GPT (Preview) in the Main sub-flow.
Edit the Display message action. For the title, enter GPT Summary and add the variable
%PredictV2TextResponse%to the Message to display box, and select Save.
Navigate to the subflow Write_notes_into_excel. Copy and paste one of the Write to Excel worksheet actions to be added to the bottom of the sub-flow.
Once pasted, edit the new Write to Excel worksheet action by double-clicking on the action. Replace the Value to write with the newly generated flow variable
%PredictV2TextResponse%. Replace the Column to write value with the columnF. Save the action when complete.
From the upper toolbar, select Save and then wait for the flow to be saved.
Run the flow by selecting Run from the toolbar to see how the GPT action works.
