Previous

Next

Exercise - Work with external data in a canvas app

Completed

In this exercise, you’ll 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’ll 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

1. Sign into Power Apps.

2. Create a new Power Apps Canvas app from blank and call it ExternalData. Select the Tablet format.

3. Make sure that you’ve added the Excel sheet (link above) to your OneDrive.

4. From the Insert button in the header ribbon, select Vertical gallery.

5. 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.

   

6. Under Add a connection, select the connection you wish to use for OneDrive for Business (it will have an email address listed under it).

   

7. At the bottom of OneDrive for Business, select Connect.

8. When prompted to Choose an Excel file, search for or scroll to the Excel file you downloaded in Step 3 and select it.

9. Check the Items table and then select Connect.

   

Your gallery is now connected to your Excel table in OneDrive for Business, and you can display the data in the gallery control.

Display and interact with your data in a gallery

1. Select the gallery you’ve added.

2. In the properties pane on the right, select the drop-down menu for the Layout property.

3. Select the Image, title, subtitle, and body layout.

4. In the properties pane on the right, select the Edit button for the Fields property and set the following points:

   • Body1: Description

   • Image2: Image

   • Subtitle2: UnitPrice

   • Title2: Name

   

5. Close the Data dialog box (select the X at the top right).

6. In the gallery, select the price in the first row.

7. In the formula bar, make sure the Text property is selected and type the following:

   "$" & ThisItem.UnitPrice

   

8. In the gallery, select the image in the first row.

9. In the properties pane on the right, select the drop-down menu for the Image Position property.

10. Select Fill and you’ll see that the picture expands to fill the dimensions of the image control.

You’ve now added, customized and configured data in your gallery.

Moving data between collections and data sources

1. Select Screen1 (anywhere outside of the gallery).

2. Let's add a button by selecting the Insert button in the app ribbon. Select Button and position the new Button1 control underneath your gallery.

3. Change the text on the button to Add to Collection (you can resize the button or the font so that the words show completely).

4. Select the button, and in the formula bar, replace OnSelect value of false with this expression:

   Collect(colItemsCollection, Items)

   colItemsCollection: Refers to the Collection name

   Items: Refers to the Data Source name

   Collect: Will copy all records from the Items into the colItemsCollection collection

   

5. 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.

6. In the Power Apps command bar, select the ellipsis just to the right of Settings and select Collections. Your colItemsCollection will be displayed in the Collections viewer. Notice that the three records from the Items data source have been added to the colItemsCollection collection.

7. Close the Collections view by selecting the back arrow at the top left of the screen.

Explore other functions that can be used with Collections

1. From the Tree view, select New Screen > Blank.

2. Select the Insert button and search for Data and select Data table.

   

3. In the Select a data source dialog pane for your new data table, select colItemsCollection.

   

   To summarize, we've now made our collection colItemsCollection as the data source for this control and not the Items table in the Excel sheet.

4. In the properties pane on the right, select the Edit fields button for the Fields property.

5. Select Add field.

6. Select all available fields and then select Add.

   

7. Close the Data pane (select the X towards the right of Data).

8. Insert a button by selecting Insert from the ribbon, select Button and position your button underneath your table. Repeat this process four times.

9. Change the text on all four buttons to Add Column, Drop Column, Show Column, Rename Column respectively. (You can adjust the button or font size to make the lettering fit in the buttons.)

10. Select the Add Column button.

11. Update the OnSelect property to this expression:

    Collect(colAddCollection, AddColumns(colItemsCollection, "Revenue", UnitPrice * UnitsSold))

    Here, we’ve created a new collection called colAddCollection, which copies all data from the colItemsCollection collection but in addition we use the AddColumns function to add a new column called Revenue, which is calculated as UnitPrice * UnitsSold.

12. Select the Drop Column button.

13. Update the OnSelect property to this expression:

    Collect(colDropCollection, DropColumns(colItemsCollection, "UnitPrice"))

    Here, we’ve created a new collection called colDropCollection, which copies all data from the colItemsCollection collection but in addition we use the DropColumns function to remove the UnitPrice Column.

14. Select the Show Column button.

15. Update the OnSelect property to this expression:

    Collect(colShowCollection, ShowColumns(colItemsCollection, "Name"))

    Here, we’ve created a new collection called colShowCollection, which copies all data from the colItemsCollection collection but in addition we use the ShowColumns function to only show the Name column.

16. Select the Rename Column button.

17. Update the OnSelect property to this expression:

    Collect(colRenameCollection, RenameColumns(colItemsCollection, "Name", "Product"))

    Here, we’ve created a new collection called colRenameCollection, which copies all data from the colItemsCollection collection but in addition, we use the RenameColumns function to rename the Name column to Product.

    When completed your buttons should look like this:

    

18. Select the Preview (or Play) button in the top right of the Power Apps editor to enter preview mode. Select all four buttons. Select the X in the top right to close out of preview mode.

19. Select the ellipsis in the ribbon again and select Collections. Then select each of the other collections you’ve created to see what effect the functions had.

    colItemsCollection

    

    colAddCollection

    

    colDropCollection

    

    colShowCollection

    

    colRenameCollection

    

Work with the Patch function

1. On the Tree View panel on the left, go back to the first screen by selecting Screen 1.

   

2. In the top ribbon, select the Insert button and select Text label. Rename the Text property to Product Name. 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.

3. With the label selected, copy it. You can use Ctrl-C and Ctrl-V to copy and paste another label (alternatively, right-click on it, and select Copy; then right-click below the label on Screen1 and select Paste). Make the Text of the label New Description.

4. 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. Your screen should now look similar to this:

   

5. Next insert a Button control and position it beneath your Labels. Change the Text property to Update.

6. Select the text input to the right of the Product Name label.

7. In the formula bar, make sure you have the Default property selected and then change the expression to the following:

   

   This label will now display 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.

8. Repeat this process for the text input control to the right of the New Description label. Make the Default formula as below:

   

9. Select the Update button.

10. 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've done. We've created a way to update our product 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 record we want to modify. It 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 have typed in the New Description text input.

Test this by previewing your app. Select one of the items in the gallery and then input a new description, then select Update. The description in the gallery changes to the new description.

Congratulations! You've learned a technique to update your data source.

Continue