With the bill tracking process now automated, let's start making all that juicy data work for us.
In this step i'll demonstrate how to bring all the information that has been extracted from the bills into PowerBI, so we can perform some basic trend analysis and track spending over time.
Creating a datasource
The first thing we need to do is persist data from the bills into a datasource that is accessible from PowerBI. Using the tools already available through my Office365 subscription, the easiest way to achieve this is through a spreadsheet stored on OneDrive.
Create a new Excel Workbook in OneDrive
Create a table with the headings that match up to the names of the outputs that were defined in previous steps, and save the document.
Edit the Take Action Flow and add a new action, and select Excel --> Insert Row from the list.
Browse and locate the file you created on OneDrive, and then specify the table name in Excel that we just created. Once selected, give it a few second and it should populate additional fields of which you defined in the Excel workbook earlier.
Fill out the form fields with the dynamic data generated from the HTTP response.
Lastly, drag and drop to reorder the steps in the flow to how you want them.
Visualize the data
Now that the datasource has been created, we have something we can attach PowerBI to.
Login to PowerBI
Create a new dataset by clicking the '+' sign next to Datasets on the menu on the left, then selecting Import or Connect to data --> Files --> Get.
Browse and locate your Excel workbook datasource on OneDrive, and click Connect.
Select the Import option on the left.
You should now see your new dataset appear on the menu on the left.
Only thing left to do now is to populate your datasource with some data, and create your report! I manually went back through my bills for the past 12 months, and added the rows into the Excel workbook so I had something to work with.
Congratulations! Your workflow is complete and you now have actionable insights that are automatically updated, and originate from an attachment in an email. Pretty cool!
What we learned
- How to create a datasource using an Excel workbook
- How to update the Excel datasource with an action from Microsoft Flow
- How to reference the datasource in PowerBI
This concludes the series, and hopefully you're now well equipped with the knowledge of how powerful these free services from Microsoft can be.
I know that the example set out in this series is rather arbitrary, but now you can start to think about how you can use these techniques to solve problems or automate processes in your personal or professional lives.
- Going with the Flow: Part 1 - Introduction
- Going with the Flow: Part 2 - Creating the Flow
- Going with the Flow: Part 3 - Saving attachments to Azure Blob Storage
- Going with the Flow: Part 4 - Parsing attachments with Powershell
- Going with the Flow: Part 5 - Working with Azure Runbooks
- Going with the Flow: Part 6 - Completing the Flow
- Going with the Flow: Part 7 - Visualizing data in PowerBI