Exercise - Work with external data in a canvas app

15 minutes

In this exercise, you work with external data in your canvas app, by adding a data source to your app, creating/modifying data by using collections, and modifying data via the Patch function.

You need to download the spreadsheet to complete this exercise. Select download once the link opens. Extract the Excel spreadsheet and upload the spreadsheet to your own OneDrive.

Add a data source

Sign into Power Apps.
Create a new Power Apps Canvas app from blank and call it ExternalData. Select the Tablet format.
Make sure that you added the Excel sheet (link above) to your OneDrive.
From the Insert button in the header ribbon, select Vertical gallery.
In the Select a data source pane at your gallery, type "OneDrive" in the search field, then select OneDrive for Business from the list. Alternatively, you could also expand Connectors and scroll through the available connectors until you find OneDrive for Business.
Under Add a connection, select the connection you wish to use for OneDrive for Business (it has an email address listed under it).
At the bottom of OneDrive for Business, select Connect.
When prompted to Choose an Excel file, search for or scroll to the Excel file you downloaded in Step 3 and select it.
Under Choose a table panel, check the Items table and then select Connect.

Your gallery is now connected to your Excel table called "Items" in OneDrive for Business. Ensure that your gallery's Items property relates to the Items table, and the gallery should then show the three entries in the table.

Display and interact with your data in a gallery

Ensure that you've selected the gallery control that you added.
In the properties pane on the right, select the drop-down menu for the Layout property. Alternatively, when you hover over the selected control, you'll see a selectable Layout button with the same options.
Select the Image, title, subtitle, and body layout.
In the properties pane on the right, select the Edit button for the Fields property and set the fields as follows (note that the controls have numbers behind them so, for example Body is "Body1", see the image below for reference):
- Body: Description
- Image: Image
- Subtitle: UnitPrice
- Title: Name
Alternatively, you can hover over the selected control and select the Fields button in the hover menu that appears over the control. Once you've made the adjustments, simply reselect the Fields button to close the option.
Close the Data dialog box.
In the gallery, select the price in the first row. Notice the Copilot button when hovering over the unit price label for the UnitPrice. Let's use Copilot to help us make this look like a price. Select the Copilot button, then select Text formatting.
In the Format text using examples panel that appears on the right side of your screen, select the input field for Desired format. You should now see some options for formatting appear. Find/select the option showing $6.19.
Once you do that, you see a suggestion for the UnitPrice field appear in the panel. Copilot wrote a formula for you that turns the value for unit price into something that looks like a price. Copilot requires you to select the suggestion, and then you can select the Apply button at the bottom of the panel.

Power Apps notifies you that it updated the UnitPrice field for you, and the values in your gallery should now all look like currency values.
In the gallery, select the image in the first row.
In the properties pane on the right, select the drop-down menu for the Image Position property.
Change the property from Fit to Fill and you see that the picture expands to fill the dimensions of the image control.

We've now added, customized, and configured data in your gallery.

Moving data between collections and data sources

Select Screen1 (anywhere outside of the gallery).
Let's add a button by selecting the Insert button in the app ribbon. Select Button and position the new button control underneath your gallery.
Change the Text on the button to Add to Collection and adjust Font size from 15 to 12 so that the text fits the button in a single line.
Select the button, and in the formula bar, replace OnSelect value of false with this expression:
```
 ClearCollect(colItemsCollection, Items)
```
colItemsCollection: Refers to the Collection name

Items: Refers to the Data Source name

ClearCollect: Clears any existing items then copies all the records from the Items into the colItemsCollection collection
Select the Preview button in the top right of the Power Apps editor (or press F5) to enter preview mode. Select the Add to Collection button and exit preview mode. Alternatively, press and hold Alt and select the Add to collection button.
In the left-side rail select the Variables button, expand the Collections option, and select the ellipsis next to colItemsCollection that you created. Select the View Table option.
You should now see a pop-up window showing the table stored in your collection. Notice that the three records from the Items data source are added to the colItemsCollection collection. You can view any of your app's collections and other variables using this technique.
Select Cancel to close the pop-up view and return to your app canvas.

Explore other functions that can be used with Collections

From the Tree view, select New Screen > Blank.
Select the Insert button and search for Data and select Data table. A data table control allows us to quickly view tabular data.
Now go to the Properties panel on the right and select your Items table as the data source. You will see Name, UnitPrice, and UnitsSold appear as columns in the control.
In the Items property for the data table control, delete Items and input: colItemsCollection
Insert a button by selecting Insert from the ribbon, select Button, and position your button underneath your table. Add three more buttons from left to right under the data table control on your screen.
Change the text on all four buttons to Add Column, Drop Column, Show Column, Rename Column respectively. You can do this simply by double-clicking on each button and typing the new name. Adjust the font size of the Rename Column button to 12, so that it fits in a single line.
Select the Add Column button and update the OnSelect property to this expression:
```
Collect(colAddCollection, AddColumns(colItemsCollection, Revenue, UnitPrice * UnitsSold))
```
As we went through this exercise, you probably noticed the Copilot icon in your formula bar. Copilot can give you an explanation for what's going on in your formula.
Select the Copilot icon in your formula bar for the OnSelect property of your Add Column button. Then select Explain this formula.

You should now see a Formula explanation similar to this image. Also notice a Copy function that you can use to add remarks to your formula.

Our example gets this explanation from Copilot: This expression adds a new column named 'Revenue' to the 'colItemsCollection' table by multiplying the 'UnitPrice' and 'UnitsSold' columns, and then collects the updated table into the 'colAddCollection' collection.

You can even copy the Copilot explanation and add it to your formula as a "remarks" line. Just add a double forward slash in front of the code "//" which tells Power Apps that your line isn't executable code.
Select the Drop Column button on the canvas, and update the OnSelect property to this expression:
```
Collect(colDropCollection, DropColumns(colItemsCollection, UnitPrice))
```
Try using the Copilot - Explain this formula again.
Select the Show Column button and update the OnSelect property to this expression:
```
Collect(colShowCollection, ShowColumns(colItemsCollection, Name))
```
Selecting this button creates a new collection called *colShowCollection, which includes all data from the colItemsCollection collection, but it uses the ShowColumns function to only show the Name column.
Select the Rename Column button and update the OnSelect property to this expression:
```
Collect(colRenameCollection, RenameColumns(colItemsCollection, Name, Product))
```
Selecting this button creates a new collection called colRenameCollection, which includes all data from the colItemsCollection collection, but by using the RenameColumns function changes the Name column to Product.
Select the Preview (or Play) button in the top right of the Power Apps editor to enter preview mode. Select all four buttons. Then exit preview mode. What we just did was to create four more collections.
Let's take a look at the four new collections we created. Select Variables>Collections, then select the ellipsis next to colAddCollection, and then View Table. Notice how your Variables pop-up window has a menu on the left side showing all four of the new collections we created, along with the original collection. You can select through each of them to see the effect on the original collection that was displayed in our data table.

colAddCollection

colDropCollection

colShowCollection

colRenameCollection

Close the Variables pop-up window after you take a look. Remember that collections are tables of temporary data only available while using the app and only available to the current user. So, once you close the app they're gone.

Work with the Patch function

Return to Screen 1 in your app.
Add a Text label control (select the Insert button and select Text label). Rename the Text property to Product Name. You can do this by double clicking the label and typing directly in the control. Then position the label next to the gallery.

Tip

An easy technique to copy and paste a control quickly is to select the control, press and hold the Ctrl key and press C; then (while holding the Ctrl key) press V. You'll see your item paste slightly below and to the right of the control you just copied.
With the label selected, copy it, and make the Text of the copied label New Description.
Use the Insert button again to insert a Text input control. Clear the default property to make it blank. Then copy this control and position both text inputs next to the labels you made in the steps above. (If you need to review placement of controls, you can scroll down to the image at the bottom of this unit.)
Next insert a Button control and position it beneath your Labels. Change the Text property to Update.
Select the text input to the right of the Product Name label.
In the formula bar, make sure you have the Default property selected and then change the expression to the following (keep in mind your gallery control might have a different name than Gallery1; change the formula accordingly):
```
Gallery1.Selected.Name
```
This label now displays the Name of the selected record from the gallery. You can test this by putting the app in Preview mode (alternatively, press and hold the Alt key) and selecting different items from the gallery. Notice how the displayed Product Name changes as you select different gallery items.
Repeat this process for the text input control to the right of the New Description label. Make the Default formula:
```
Gallery1.Selected.Description
```
Another thing to note is the dark vertical bar that displays on the left side of the gallery item that is selected.
Select the Update button.
Change the OnSelect property of the button to this expression:
```
Patch(Items,LookUp(Items, Name = TextInput1.Text), { Description: TextInput1_1.Text })
```
Note

Your text input controls may have different names than this example. Adjust your formula accordingly.

Let's recap what we did. We created a simple way to update our selected product's description.

Patch - Provides the means to modify or create a record in your data source, in this case we're modifying a record.
LookUp - Uses our product name to find the specific record we want to modify. LookUp returns the first record that matches our condition: Name = TextInput1.Text.
Once Power Apps finds the matching record (via LookUp), it updates the Description of the item in the data source based on what you typed in the New Description text input.

Test this by previewing your app. Select the Cinnamon item in the gallery and then replace the current description with "Cinnamon flavored Green Tea", then select Update. After a moment, the description in the gallery changes to the updated description.

Screenshot of final output form with change to Cinnamon.

Congratulations! You've learned a simple technique to update your data source via Patch, and how to use/modify your data within the app by employing collections.