If you work with Power Apps in Power BI you probably already had the need to use a premium connector. But what if you don't want to use it?
Well, as with everything in life, there is an hack for that. That hack is called URL Parameters!
In this post I will show how you can use Power Apps in your Power BI report using only URL parameters and a SharePoint List. This will work for any Power BI data source, yes, you heard it right!
The ultimate goal is to allow the user to write comments in Power BI through a Power App. All without having to connect your Power App to a premium connector. Magic!
1) The first thing you need to do is create a SharePoint List. As this is not the focus of this post I will leave you with a link where you can follow the steps if you don't know how to do it. Don't panic, it's fairly easy!
In my list I added the fields Purchase Order and Comment. The remaining fields you see in the list bellow are automatically created by SharePoint and I just made them visible.
Note: The Purchase Order field needs to allow duplicate values, otherwise you will only be able to create one comment per Purchase Order!
2) The second step is to connect to your newly created SharePoint list in Power BI.
To do this click on Get Data and then choose SharePoint Online List:
A popup window for you to enter your SharePoint list URL will show.
Note that this is the SharePoint Site URL and not the entire URL you have in your web browser!
The SharePoint List URL should be similar to this: https://companyname.sharepoint.com/sites/site-name
Select to login with your Microsoft account:
3) After you successfully entered your credentials you will get a list of all the available content you have in your SharePoint Site. Select the list you want, in my case I selected Purchase Order Comments, and click Transform.
You will now be able to see the complete list, with a lot more columns than the ones you initially added... Don't worry, like I said before these are automatically generated by SharePoint (note: I strategically added some rows to the comments list before hand).
In my case I wanted to get the name of the person that will add the comment in the Purchase Order, so I extracted the values FirstName and LastName from the Author column:
I then merged the two columns to get the full name of the user and removed most of the other columns as I won't be needing them.
My final SharePoint Comments table looks like this now:
In this case the Title column corresponds to my Purchase Order column and it's the column I will be using to connect this table with my Purchase Orders table.
4) Next I loaded my Purchase Orders table from SQL Server. The table looks like this:
I want to add comments to specific Purchase Orders using a Power App, the problem is: if I try to build a Power App connected to SQL Server..... well, you guessed it, it's a Premium connector! So unless you pay to use it you will have to do a workaround.
5) For everything to work seamless we need to add a relationship between the Comments table and the Purchase Orders table. The relationship is done between the Purchase Order Number column from the Purchase Orders Table and the Purchase Order column from the Comments Table:
In this case the relationship shows the cross filter to both directions and the cardinality as 1 to 1, but the cardinality should be one to many and the cross filter direction single.
If for some reason you end up with a many to many relationship and your model has some degree of complexity you should try to use a bridge table (useful link bellow):
6) We are now going to build our Power App. Unfortunately, as we will be using URL Parameters, we will not be able to embed it in the report itself, it will open in a new window but I guess it still does the job right?
Open Power Apps and click on Start Data with SharePoint:
A new window will open. You then select the SharePoint site where you’ve built your list on:
After selecting your SharePoint site you will then see the lists available. Select the one you want to use, in my case is the Purchase Order Comments List. Select your desired list and click on Connect.
7) Power Apps will automatically create an app based on the information from your Sharepoint list. You can see that it created three different screens. As we don’t need all of them we will delete the BrowseScreen1 and the DetailScreen1 as we will be only working with the EditScreen1:
We now have only the Edit Screen available, as we want to make our Power App as simple as possible (you can get creative of course, but that will maybe be a story for another post).
8) Because you deleted the Browse and the Detail screen, a few errors will show up:
Don’t be scared! This is because the Edit Screen is still referencing the Browse screen. To solve this you just need to click on EditForm1 in the Tree View (1) and select Item in the dropdown (2). You can see the reference to the deleted BrowseGallery1 in the formula bar (3). To remove the error just delete the reference (delete everything from the formula bar, it needs to be empty).
The error should have disappeared now.
9) There is a field called Attachments that we don’t need, delete this field. You should end up with your Power App looking like this:
You can make it look nicer of course, but I will leave that one to you folks 😊
Save your Power App with any name you like. I saved mine as Purchase Order Comment.
10) We will now get to the juicy part of this tutorial: how to pass a URL parameter to the Power App!
If you go back to Power Apps landing page and click on Apps (1), this will show you the list of all your saved Power Apps.
On your App select the tree dots and click on details (2).
You will then be able to see the App URL. Copy the URL link.
11) We will now go back to our Power BI report and do a little trick using the Power App URL. As we want to pass the Purchase Order as a parameter in the App URL, we will update our Power App URL to include the Purchase Order number as below:
https://apps.powerapps.com/play/7d77ba66-3a9a-43b2-8f25-eb6a8d558d22?tenantId=ce56fae6-055d-4c9f-b6c9-9d341506a491&PurchaseOrder=d1084465PurchaseOrder is the name of our URL parameter!
To get the context of each one of the purchase orders we will Add a New Column called Comment URL to your main table (In my case it’s the Purchase Orders table from SQL Server) concatenating the App URL with the Parameter Name and Value:
Comment URL = "https://apps.powerapps.com/play/7d77ba66-3a9a-43b2-8f25-eb6a8d558d22?tenantId=ce56fae6-055d-4c9f-b6c9-9d341506a491" & "&PurchaseOrder=" & 'Purchase orders_Rochdale Borough Council'[Purchase Order Number]I’ve added this new column to the main table in my report and changed the data category to Web URL:
Passing parameters to an App URL is as simple as passing parameters to any HTTP URL.
You can pass multiple parameters to an App URL by separating parameters using “&” ampersand.
12) By default you will see the full URL in the table. To change this and make it tidier go to the Table Format Tab and click on URL Icon. You will now see only an icon and not the full URL.
If you want to go a step further and customised description to the URL and not an icon have a look at this Microsoft post here:
13) Now we need to consume this Parameter in the App. Go back to your Power App but first, we need to do a few changes to the Purchase Order field. If you click on it, it will show as locked. To unlock it select the Purchase Order data card value (1), go to Advanced and click on Unlock to change properties (2).
Click on the Purchase Order Data Card and in the drop down select Default:
We will change the Default of the card to our newly created parameter PurchaseOrder:
Whenever you click on any URL in Power BI, the Power App default value for the Purchase Order will update to show your selection!
14) One final thing on the Purchase Order card: let’s disable the Purchase Order editing as we don’t want our users to be editing this field when the App is being used.
Still with the Purchase Order Data Card selected, go to the drop down and select DisplayMode:
Instead of Parent.Display we will tell our App that this field is not editable by adding in the formula tab DisplayMode.View:
15) We also need to change the Comment Data Box as we want it to be empty for users to add new comments every time they click on a URL in Power BI.
Follow the same approach as we did before in step 13.
Now select Default on the drop down and delete the Parent.Default formula leaving the Default empty:
16) We ‘ve been using the EditForm from the SharePoint template that Power Apps built automatically. This means that currently our form is built to Edit entries and not to Add New Ones.
To change the app so it adds a new comment on our selected Purchase Order we need to select the EditForm box (1), and with the DefaultMode selected in the drop down (2) change the FormMode in the formula bar to New instead of Edit (3):
17) To finalise our App we will just add a Success Screen so that whenever the user submits a comment he knows this comment was successfully registered. To do this go to New Screen and click on Success.
You will have a screen (named Screen1 in this case) that will have a success message when the user submits the form.
Now we just need to change the navigation of the submit button so it shows Screen1 (the success message screen) when you click on it. Go back to our main Comments Screen and click on the submit button (1).
Select On Select in the drop down (2) and change the formula adding Navigate(Screen1) (3).
Save and publish your Power App.
And we are finished! We can now add multiple comments to Purchase Orders and make these comments appear in Power BI for users to keep track on them. All of this without having to use a Premium connector!
Note: You need to give users access to the Power App otherwise they won’t be able to use it!
Have a look at the report I created on my GitHub:
I know what some of you might be thinking… “It would be nice to have the possibility to edit the comments also!”. This will probably be a topic for another post 😊
Cheers!
Mara