Exercise - Create an editable Excel file for bulk validation

Completed

In this exercise, you'll apply your new knowledge to create an Excel spreadsheet to bulk validate data.

Scenario

You're responsible for your organization's Dataverse environment. The marketing department manager requires validation of street addresses before they mail a new promotional brochure to potential franchise owners.

To proceed, you'll provide an Excel spreadsheet to a colleague who will use different address validation tools that are specific to different states.

Prepare Dataverse and the data

For this exercise, data will be exported from the Account core table.

A model-driven app with at least one view for the Account table is required. If it isn't already available, you can create it by following these steps:

1. From the Power Apps portal, select Create, then Blank app, then Blank app based on Dataverse, and Create.

   

2. In the Name field, enter a name for the app and then select Create.

   

3. Select Add page in the left navigation pane and then select Dataverse table as the page type.

   

4. In Select a table, select Account. Select Add to proceed to the next step.

   

5. Finalize the creation of the app by selecting Save and then Publish in the upper left hand corner of the screen.

   

Now, the application will be available so that you can proceed with this exercise in Microsoft Power Apps Studio.

If you've already gone through the Add new accounts from a text file exercise in the Use Power Query to load data in Dataverse module, you can skip to the next section of the current exercise.

To identify accounts as potential franchise owners and import records into the Account table, proceed with the steps in Exercise – Add new accounts from a text file.

Exercise

You now have a model-driven app that contains navigation to the Account table, which contains the column to identify the potential franchise owner. Next, you can follow these steps to export the required data to a dynamic worksheet for bulk validation:

1. Go to any available view for the Account table. Select Export to Excel and then select Dynamic Worksheet. (You may need to select the 3 ellipsis at the top of the screen to see the option to Export to Excel.)

   

2. Select the following columns to export:

   • Account Name
   • Address 1: Street 1
   • Address 1: Street 2
   • Address 1: Street 3
   • Address 1: City
   • Address 1: State/Province
   • Address 1: ZIP/Postal Code
   • Potential Franchisee

   When you're finished, select Export.

   

3. Open the generated Excel spreadsheet. You may need to retrieve it from your Downloads folder. If a notification message is displayed, select Enable Editing.

   

4. Depending on the security settings, you might also receive a notification message that requires you to select Enable Content.

5. The scenario requires that you filter the rows for the potential franchise owners.

   

6. To validate the addresses in bulk and specify the logical sequence, apply a sort on the Address 1: State/Province column.

   

Next steps

You've now learned how to export Dataverse records to Excel and manipulate this data to complete scenarios where bulk validation might apply. Next, you'll learn how to update Dataverse after you've edited the records in an exported Excel spreadsheet.