Exercise - Create a custom table and import data

  • 15 minutes

Scenario

The current sales process for your company is manual and updates are only provided each Friday. To simplify this process, minimize the opportunity for mistakes, and improve visibility, you've decided you want to create a new app to track sales leads and automatically calculate the forecasted revenue. You want Dataverse to store the list of potential customers.

Use Microsoft Dataverse to store data

In this exercise, you'll use Dataverse to store the list of potential customers for your app.

Creating a custom table

  1. Go to the Power Apps maker portal and sign in (if necessary).

  2. On the menu, expand Dataverse and select tables.

  3. Select New table.

  4. Under Properties enter the Display name: Prospects

  5. Select Primary column and enter the Display name: Prospect Name

  6. Select Save.

  7. After a few moments, the Tables>Prospects screen will appear. Select + to add a new column in the Prospects columns and data section.

    Screenshot of Prospects table main page.

  8. In the New column pane, enter the following information:

    • Display name: Stage
    • Data Type: Select Choice (and select Choice from the popup)
    • Required: Select Business required

    Screenshot of New column pane showing entry fields

  9. Under Sync this choice with, select New choice. The New choice pane will appear. Enter the following information and select Save.

    • Display name: Prospect Stage

    • Add the following choices under Label, selecting New choice between entries until your table looks like the screenshot below. When completed select Save:

      • Lead (under Value enter 1 . Subsequent entries will automatically number.)
      • Opportunity
      • Won
      • Lost

    Screenshot of new choice pane showing four choices entered.

  10. Once you've saved your New choice, select the dropdown under Sync this choice with and find/select Prospect Stage.

    Screenshot of dropdown list showing Synch this choice with options and Prospect Stage highlighted.

  11. Under Default choice select Lead

  12. Select Save.

  13. Select + in the Prospects columns and data pane to add a new column.

  14. Enter the following information in the New column pane and then select Save.

    • Display name: Contract Amount
    • Data Type: Currency

  15. Select + again to add a new column with the following information and then select Save.

    • Display name: Probability
    • Data Type: Whole Number (Select Number and then Whole number from the popup)

  16. Add one more column with the following information and select Save.

    • Display name: Forecasted Revenue
    • Data Type: Currency
    • Behavior: Calculated

  17. Next, we're going to edit the behavior our Forecasted Revenue column by selecting the column header and selecting Edit Column from the dropdown.

  18. In the Edit column pane, select the Edit button under Behavior. A popup window will appear with the Calculated Field - Set Forecasted Revenue where you can add an Action. (If it doesn't appear the first time you select the Edit button, repeat steps 17 and 18 until it does.)

  19. Under Action select + Add Action and enter the following formula, but don't copy and paste. Type it in. Your column names won't be exactly the same as the example below since the crXXX_ will be specific numbers and letters that define your environment. The formula entry will auto-suggest options as you start to type the column names.

    crXXX_contractamount * (crXXX_probability / 100)

    Screenshot of the calculated field action entry point

  20. Select the circled checkmark to save your changes. You may need to scroll right to see it.

  21. Select SAVE AND CLOSE and the window disappears to reveal your Prospects Tables screen again.

Add a business rule

  1. In the table designer, find the Customizations pane on the far right and select Business rules.

  2. Select + New business rule at the top of the screen; a new browser tab will open.

    Screenshot of new business rule browser window.

  3. Select the Condition New Condition from the design pane.

  4. In the right-hand pane, for Field, ensure that Contract Amount is selected.

  5. For the Operator, choose Contains data.

  6. Select Apply.

  7. In the right-hand pane, select the Components tab.

  8. Click and hold Set Business Required and drag to the plus symbol to the right of the purple checkbox in the design pane.

    Screenshot of drag set business required action to new condition.

  9. With Set Business Required New Action selected/highlighted, in the right-hand pane, for Field, choose Probability.

  10. For the Status, choose Business Required.

  11. Select Apply.

    Screenshot of the properties pane of the Set Business Required with Field and Status dropdowns selected, along with the Apply button.

  12. In the top left of the screen, select the dropdown next to Prospects New business rule and set the Business rule name to Make Probability Required.

  13. Select Save in top-right corner of screen.

  14. Select Activate to (also top-right of screen) activate the rule.

  15. Select Activate in the popup to confirm activation.

  16. Close the browser tab.

  17. Notice your new business rule has been added to the chart. Select Prospects to return to your table editing screen.

    Business rules chart showing your new rule: Make Probability Required. The Prospects table name is highlighted.

Importing Data from an Excel file

You'll use the Excel spreadsheet named Prospects for this exercise. Open the link and select the Download button and save it locally.

  1. Open the file. Notice the Stage column is empty. You'll need to look these up and enter them manually.

  2. You'll need the Power Apps maker portal open; if you closed it, go to the Power Apps maker portal and sign in to Power Apps.

  3. Go back to your Excel file and in the Stage column, enter the values as below:

    • Contoso Flooring: Won
    • Fabrikam Inc: Won
    • Adventure Works: Lead
    • VanArsdel: Lost
    • Adatum: Lead
    • Relecloud: Opportunity

  4. Save and close the Excel file.

  5. Continue (or find) with the Prospects table.

  6. Select the Import button at the top of the screen and select Import data from Excel.

    Note

    If you receive an error when selecting the Import data from Excel option, then your Power Apps license does not allow for importing Excel data.

  7. Select Upload under the File entry field, locate the Prospects Excel file, and select Open. You'll see an error notification under the column Mapping Status saying "Mapping errors exist".

  8. Select Map columns. Map the following Prospect columns to the associated Source values:

    • Contract Amount: ContractAmount
    • Prospect Name: Name
    • Stage Value: Stage
    • Probability: Probability

  9. Select Save Changes at the top right of the screen. If successful, you'll see a success notification under Mapping status saying "Mapping was successful".

  10. Select Import at the top right of the screen. If successful, you'll see a notification saying "Import completed successfully." Close the window by selecting x at the top right of the window.

  11. Refresh your browser screen.

  12. Ensure that the data has been successfully imported.

Congratulations! You've created a custom table with a business rule and imported from an Excel data source. Now you have an automated way to store your potential customers.