Writing Back in Power BI
Nick Hilton | 13 October 22 | 4 min read
This week’s post is going to be a bit shorter than last week’s mega essay on how to get started in Power BI. I know I post a lot of content where the aim is to really walk you through how to do something you already know you want to do but sometimes there are areas which most people don’t know about, or more specifically in this case, things Power BI can do that most people think it can’t.
Power BI (quite fairly) is considered to be a one-way tool for reporting. Your report designers and those closer to the backend will deal with getting the data in, a report will be made, and then the report will be consumed. Most of the time people just want good reports so that they can access and understand the data driving their business, but as report designers we need to understand how Power BI is much more than an app for creating and viewing reports but instead a tool that can be used to quickly extract data, to drive automations, and even write back information to the underlying data sources, which is what we’re going to focus on today.
I’ve been able to identify two ways to open your reports up to users being able to enter data. The first is the proper way and should you ever need to create a solution that incorporates writing back then this first way is the way to do it, the second is a bit more of hacky work around but crucially it doesn’t involve any more licences and should be accessible to anyone with a OneDrive or SharePoint account.
Solution 1 – Using an embedded Power App
Power Apps is an extremely functional piece of Microsoft’s Power Platform, if you’re unfamiliar with Power Apps it’s essentially a tool to make creating simple data-based apps accessible to users who aren’t software developers. It’s not going to be helpful in making the next Facebook, but it is extremely powerful for organisations who needs quick apps which can streamline things like managing stock, requesting holidays, or even scanning in and storing documents. (For some amazing examples check out the community gallery here – I love the Star Trek one!!)
Here is an example of a simple report that is just showing a table of users registering for an event, but alongside that table is a Power App where further entries can be added immediately:
And just like that it’s no longer just presenting data it’s a functional tool. The user in Power BI enters the data to the Power App and when they click ‘add’ the data is added as to the underlying data sources. The app writes to those sources and then as normal when Power BI refreshes (and that could be instantly with direct query) then the table shows the extra row.
From the Power BI side of things Power Apps is just another visual to drop on to the page, the complexity lies in creating the app, but it really is made to be accessible and there’s plenty of excellent material online to guide you.
The one requirement to consider here is that Power Apps requires its own licence, and that’s per user just like Power BI. So, a Power App in a report that’s distributed throughout a business could be quite costly, but if you’re using it to empower individuals, small groups, or specific functions like a computer monitoring one machine in a production line then the value on your investment is going to be massive.
Solution 2 – Using Excel in a shared location
As mentioned before this is nothing like the functionality provided by a Power App. You could engineer a solution in your ETL to provide similar functionality I suppose but that seamless experience for the user disappears.
Imagine you’re creating a report for senior management to review key figures. Analysts within the business will work with the data in the report and create commentary that shows up on each page alongside the other visuals. In this situation we could create a table in Excel for that commentary and store that Excel file in a location like OneDrive or SharePoint.
Or alternatively imagine that you’ve got a report which outputs key figures for performance but that those key figures depend on data you must add each day. Well, if the table that contains that data is stored in Excel and that file is accessible then each time it needs to be changed you can go in make your adjustments and see the results change on the next refresh.
This may sound a bit obvious, and you’re possibly thinking ‘but this is literally just how you use Power BI’ but I’ve found that as you advance in BI you generally have a disposition to avoid potentially inconsistent data sources like Excel. When the alternatives can involve ever more licencing costs and complexity though sometimes it can be better to look at simpler solutions.
This works beautifully in an app as you can have that Excel file sitting right next to the report in the Power BI service, check out my post on that here.
P.S : Okay maybe I’m wrong about Power Apps’ limits, someone who describes themselves as the ‘Head of the Power Platform Orchestra’ used it to make a functional ‘Glass Harp’. It’s amazing and they’re blowing my mind: