Collect/Analyze/Automate with the Power Platform

Stephanie and I are data people. We excel at taking loads of unstructured data and making it intelligible (see what I did there?!).

Do we want to be working with unstructured data? Is Excel a data collection tool? Or better yet, is Excel a database? In theory the answer to all of these questions is of course, NO. But in practice, we do the best with what we’re given and we move along. I know you do this too! The one place where I realized that I had a bit of control was in the data collection piece, which is where this solution began.

I thought I’d share a how-to on building an end-to-end solution using the Power Platform. Did I set out to build a data collection tool? Again, NO! But I wanted to avoid the headaches of data collection in excel and end-users wanted to see data immediately, so here we are.

What is the Power Platform?

Microsoft’s Power Platform was introduced in the last several years in bits and pieces, with Power BI debuting in 2015. Power Apps and Power Automate (formerly known as Flow) followed in 2016, and the final piece of the puzzle was introduced last year, Power Virtual Agents. Together these products form a robust low-code/no-code business application product. Okay…so what does all of that really mean? Here’s what it means to me:

I use Power Apps to collect data with robust validation. I use Power BI to visualize said data. And I use Power Automate to bring it all together. Power Virtual Agents haven’t come into my universe yet – but they’re essentially smart little chat bots that can be built by any of us.

Full disclosure, it took me a minute to really get on board with Power Apps and Power Automate. I found Power Apps particularly difficult to learn; partially because there were few community resources at the time and partially because it was unlike any other formula language I had ever known. But the result was that I turned a clunky, manual, Excel based process into a Power App – and I did it with a LOT of help! Thanks to Rishi Sapra for helping me with the initial VBA on the Excel sheet and Phillip Guiglielmi and Merkeb Michael for helping with the Power Apps prototype.

These tools have now been made fool-proof – even enough so that I can do it myself 😉

How can you use it?

There are loads of getting started resources out there for all of the Power Platform products. This post assumes a bit of basic knowledge about Power Apps, Power BI, and Power Automate, but I mean basic. Microsoft has made it pretty easy on us to click our way through it! Here’s the solution in 5 easy steps! No joke – 5 steps!

In this initial build, I kept it easy and used a SharePoint list. I would recommend a SharePoint list if you’re new to the Power Platform, solely for the fact that it natively integrates with Power Apps and you can build your app with a few clicks of a button.

In SharePoint, add a New List, name it, and click Create.

Set up the columns in your SharePoint list exactly as you want them to be collected. If you’re building this for the very first time I would recommend picking just a few fields to collect – after you have them set up and you get the whole solution functional, you can go back and add to your list. In this case, I am collecting data about my employees and their work from home status.

If you’re new to working with SharePoint lists, they can be somewhat tricky. For your first test keep it simple and collect a text and a numeric value to see how they will appear in Power BI. I would also recommend not using spaces in your column names.

Remember how I said that building a Power App has been fool-proofed?! It really has! Simply click on Power Apps in the SharePoint menu and you’ll be dropped straight into the Power Apps studio!

This part is crazy….Power Apps builds out the whole functional app for you. Did you hear that – I said functional! You can immediately start to test your app by clicking on the preview button in the top right hand corner (or pressing F5) or you can get fancy and customize your app. I would recommend (at the very least) creating a landing page for whoever will be doing data entry.

Test your Power App and see what happens!

The reason I used a streaming dataset in Power BI rather than just setting up a regular refresh was that end-users wanted to see the data immediately, with zero lag time. 99% of the time I am able to talk users out of this (because there are some limitations with a streaming dataset and it’s not always necessary), but in this case it’s what we did.

When testing in Power BI, I like to play around in my own sandbox, in what is called “My workspace.” You can build a streaming dataset in any workspace, but for your first test, I would recommend using your own workspace. Note that you do NOT want to use your own workspace for anything that you intend to share and/or collaborate on with others – this is simply your space to play.

In your workspace, select New Streaming dataset, use the default streaming dataset type (API), and hit Next.

Name your dataset and enter values that correspond exactly to the values in your SharePoint List. Ensure that the data type is set correctly. Most importantly, toggle the Historic data analysis to On. After you ensure everything is set the way you want it hit create and take a look at your new streaming dataset!

I absolutely LOVE Power Automate for workflow automation and the templates that are included are incredible! If you know what you’re trying to accomplish, you can easily search for a template by keyword or by product. I started my search here with Power BI which led me to a template that I have used again and again, which adds data from a SharePoint list to a Power BI streaming dataset any time a new value is added. This template just so happens to be called “Visualize classroom walkthrough data in Power BI” – but you can rename it and make it your own. If you have not checked out the templates I highly recommend it!

Power Automate will ask you to confirm the credentials of whatever systems you are trying to use. Hit continue and then we will create some custom code click some buttons to tell the Flow exactly what we want it to do! This is super slick.

All you need to do is use the dropdowns to define the SharePoint and Power BI top level information. We will then add dynamic content to each of the data fields, essentially telling the flow which SharePoint List fields to drop into which Power BI streaming dataset fields. The last step is to remove the Payload – we do not need this for this particular flow. Save your flow!

Add another entry to your Power App and test your Flow to see if everything is functioning as you would expect. You can test your Flow (in the top right hand corner of the Power Automate interface) or you can simply add data in your Power App. When you navigate to the Flow window you should see this!

The final step is to jump back into Power BI and build a report using your streaming dataset! Note that any data you entered when testing your Power App prior to setting up the streaming dataset will not appear in Power BI. This is because we have it set up to add new rows to the Power BI streaming dataset when new rows are added to the SharePoint list, and your test entries were already in the list. If you want to use a SharePoint list as your data source and set up a regular dataset refresh in Power BI, you certainly can, but that is not this solution.

In your workspace, find your streaming dataset and click on the ellipses next to the dataset name. Select create report and you’ll see the Power BI report canvas. Note that in the Fields pane on the right-hand side of the screen you should see the data fields that you defined in step 3 above.

You can now start dropping visuals on the canvas. Take note of how text vs. numeric vs. date/time fields appear.

Save and publish your report! You’ve done it! You have created an end-to-end solution using the Power Platform.

If you have questions or comments, please feel free to drop them below. I hope that you find this post helpful!

Note, this blog post accompanies a presentation that I gave at Collab365’s GlobalCon4 pre-conference Turbo Tuesday session (that’s a mouthful)! There is another Turbo Tuesday coming up on November 11th – it is completely free of charge and has some incredible M365 content. These are fast, fun, how-to sessions and if you miss anything, you can subscribe to get links to all of the recordings!

Double, double toil and trouble;
Fire burn and caldron bubble.