Let’s pick back up where we left off last week and continue building the workflow and finishing up our PowerApp!
Create a Flow
At this point, we have a functioning app that stores data in a SharePoint list. The Action Log app is complete, and if you wanted to stop here, you could. But, what if you wanted a way to review the action log in a more substantial capacity, get insights from the data, and take action on it. Well, that’s what we are going to do next.
In this section and the next, we are going to add a Flow to the app to manually send a reminder to someone that they have a past-due action item. Then, we will create a Power BI report to analyze the data and in which we will embed the PowerApp we just created. First, on to Flow.
Start by creating a blank flow.
Search for PowerApps, and select PowerApps in the Trigger section of the second box.
Then, click on New step, and search for “send email.” Select “Send an email (V2) (preview) Office 365 Outlook” under the Actions section.
Click in the “To” field. A box will appear to the right. About a third of the way down on the right side of that new box, click “See more” and select “Ask in PowerApps.”
Click in the Subject field, select “See more” as before, and click “Ask in PowerApps.” Once more, click in the Body field, select “See more,” and click “Ask in PowerApps.” Name the flow “Power Platform Demo” (in the upper left) and save it (in the upper right).
What we have just done is to create a flow that is waiting for a PowerApps trigger. That trigger will supply three parameters: the email address to which the email is to be sent, the subject line of the email, and the body of the email. We will now go back to the PowerApp, create a button, connect it to this Flow, and specify those three parameters.
Back to the App
Now, go back to PowerApps and to DetailScreen1. Add an envelope icon by clicking on Icons under the Insert ribbon.
Move the icon to the blue header bar, just to the left of the trashcan, and set the Color property to White.
Then, open the Action ribbon, click on Flows, and select the Flow you just created.
You then pass it the three parameters as described above. Here is the script:
PowerPlatformDemo.Run(BrowseGallery1.Selected.’Responsible Email’.Value, Concatenate(“Please check action due “, Text(BrowseGallery1.Selected.’Due Date’, DateTimeFormat.ShortDate), “, “, Left(BrowseGallery1.Selected.Action, 20), “…”), Concatenate(“Please check action due “, Text(BrowseGallery1.Selected.’Due Date’, DateTimeFormat.ShortDate), “, “, BrowseGallery1.Selected.Action, “…”))
Save the app, name it Action Log, and publish it.
Now, On to Power BI
Open Power BI Desktop, select Get Data and pick SharePoint list.
Enter the URL for the SharePoint site which contains the list, and select OK.
Pick the list Action Log, and click Edit.
In the interest of keeping the M script tidy, you can delete the last step, Rename Columns; all it does is rename a column from ID to ID.1, and we’re going to delete it momentarily. Select the columns you would like to keep; for me, they are Meeting, Date, Issue, Action, Status, Due Date, Responsible Email, and Complete. Then, select the Remove Columns dropdown menu from the Home ribbon, and select Remove Other Columns.
Lastly, assign datatypes to all of the remaining columns. Close & Apply the query, and wait for the data to load.
We will create two simple calculated measures: one to count how many actions there are and another to concatenate the action descriptions together. Here is the DAX for the first calculated measure:
Count of Actions =
COUNTROWS ( ‘Action Log’ )
Here is the DAX for the second one:
Actions =
CONCATENATEX ( ‘Action Log’, [Action], UNICHAR ( 10 ) )
The report that we will create will, in the interest of time, be quite simple. Create a stacked column chart that takes up the full height and the left two-thirds of the canvas. Place Meeting in the Axis well, Responsible Email in the Legend well, Count of Actions in the Value well, and Actions (the newly-created measure) in the Tooltips well. Add the Boolean field Complete to the visual filter, and set it to false; this way, people will only review actions that are still in progress. Add a PowerApps visual in the remaining space. (If you don’t have that visual available, please see instructions here.) Here’s what my report looks like at this point.
Save and publish the report.
Go to the report page in the Power BI service, and select Edit report.
Add Meeting and Responsible Email to the PowerApps Data well.
This defines what data the Power BI service will pass along to the app so that you can cross-filter the app along with the rest of the report, but first, click “Choose app” in the PowerApps visual.
Select the Action Log app from the list of apps, click Add, and click “Go to PowerApps Studio.”
This will take you back to the app. Go to BroseGallery1 on BrowseScreen1. Change the Items property to the following:
SortByColumns(Filter(If(IsBlank(PowerBIIntegration.Data), ‘Action Log’, Filter(‘Action Log’, ‘Responsible Email’.Value in PowerBIIntegration.Data.’Responsible Email’)), TextSearchBox1.Text in Action, !Complete), “Due_x0020_Date”, If(SortDescending1, Descending, Ascending))
If you get red squiggles under the phrase “PowerBIIntegration”, look to see whether you see PowerBIIntegration in the Screens section.
If you don’t, then you need to remove the PowerApps visual from the Power BI report, re-add it, add Meeting and Responsible email to the PowerApps well, and click “Go to PowerApps Studio” all over again. That last piece is necessary to “turn on” Power BI Integration in the app. If it is not turned on correctly, try repeating that process to see if you can get it turned on.
Once you paste that formula into the formula bar, be sure to use the Format text feature, and let’s go through it starting with the first If statement. The IsBlank(PowerBIIntegration.Data) function is testing whether any data is coming from the Power BI service. The IsBlank() function is a Boolean, so it returns a True or a False. If the app is not receiving any data from the Power BI Service, then it returns the entire Action Log list; if it is receiving data from the Service, then it filters that list based on the data that is coming over.
Why is this important? It is essential that the app be functional both in reports and standalone. Without this piece, if you had the PowerBIIntegration component without the If statement, it would be looking for data from the Service that would not be provided, at least in the standalone version. This algorithm is essentially testing whether the app is standalone-mode or in the Power BI Service and then filtering down the Action Log list, or not, based on that consideration. The rest of the script is as you’ve seen before: testing for whether the field Action contains what is being searched for, filtering out the complete actions, and sorting by Due Date.
Finale
Save and republish the app. Go back to the Power BI report. You should now be able to cross-filter the stacked column chart with the embedded PowerApp.
There ya’ go! The epic journey is complete. Look, combining all these functionalities takes some time, and it takes a while to properly architect a solution. Imagine if you had multiple tables, lots of screens, multiple report pages, and so on. Also, there are services out there that can do this and more. The point isn’t to teach you how to create your own Action Log app, though this is certainly better than the ones I used to use. The point of this is to get you thinking about what’s possible. Imagine what problems you have in your work/life. Put these tools and techniques to solving those problems. We have only just scratched the surface. Pick a problem, imagine a solution, and there’s a decent chance you can build it. I look forward to seeing what you all come up with! Until then!
Fin
Where It’s At: The Intersection of Biz, Human, and Tech*
We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”
In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂
* – unless, of course, you have two turntables and a microphone. We hear a lot of things are also located there.
Your email address will not be published.