Experimenting with Flow and PowerBI

Office 365 includes lot services, of which many are often over looked in favour of Exchange/SharePoint/Teams. I just want to share a recent example of where I’ve tried two of the others, Flow and PowerBI, neither of which I’ve tried to use before.

Being a movie fan I have a yearly unlimited membership to a national cinema chain and had previously been updating an excel document manually to record visits to see if I “break even” vs the cost of membership but thought there must be a better way to record my visits. The answer of course, was to use Flow.

With Flow, amongst many other things, it is possible to access emails in a mailbox (including Exchange and Gmail) and perform functions based on their content, given I get an email every time I make a booking this seemed the perfect place to start. The first hurdle I hit was that the booking details in the email where not actually included in the information flow retrieved as it was content hosted on the cinema’s booking website so I could only work with the subject of the email which fortunately included the movie name.

I used a combination of Compose steps, where I could manipulate the contents of the emails subject to extract just the movie title, as well as separating out the received month and year into different compose steps where they could be formatted as needed. The compose step also enables the use of Boolean expressions which allowed me to identify whether the movie was in IMAX or not based on the subject of the email containing IMAX or not.

Once all these steps were complete, Flow would then write the information to an Excel document located in my OneDrive and then delete the email.

All the steps created in flow were made in the Web browser and it produces as nice visualisation of the process.

Once the information was in Excel I then wanted to be able to process and display it, the tool available in Office 365 that enabled this for me was PowerBI. I had two options available for accessing my excel document, manual import or automatic (there is however a slight delay in import but you don’t have to go back and click a button) so I choose the automatic import option.

Using the intuitive web based console I was able to create visualisations using the data in my spreadsheet within minutes, with PowerBI automatically performing sum calculations on some of the fields. I found it was fairly straight forward to choose the type of visualisation and then it was just a case of dragging the field over to it.

After a few minutes of experimenting with the range of visualisations available I was able to produce my own dashboard for viewing within the browser. In addition to viewing it in the browser I found I was able to subscribe to updates via email so that I get an email with an updated version every time it automatically updates.

I am by no means a Flow/PowerBI power user and there may well be more efficient ways to do the following but this was a fun learning exercise for me and I just want to show what can be done with a few minutes work and no formal training in the products.


About the author