Exercise - Create a custom table and import data

Completed

Scenario

The current sales process for your company is manual and updates are only provided each Friday. You want to simplify this process, minimize the opportunity for mistakes, and improve visibility. So you decide 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 use a custom Dataverse table to store the list of potential customers for your app.

You learn how to:

  • Create a custom table.

  • Add custom columns to your table.

  • Create a formula column (calculated).

  • Configure a business rule.

  • Import data from a Microsoft Excel workbook.

Create a custom table

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

  2. On the menu, select Tables.

  3. Select New table and select Set advanced properties.

    Screenshot of new table option.

  4. Under Properties, enter the Display name: Prospect.

  5. Select the Primary column tab and change the Display name to Prospect Name.

  6. Select Save.

  7. After a few moments, the Tables > Prospect screen appears.

    Screenshot of Prospect table main page.

  8. Select New > Column to add a new column.

  9. In the New column panel on the right, enter the following information:

    • Display name: Stage

    • Data type: Select Choice (and select Choice from the pop-up)

    • Required: Select Business required

  10. Under Sync this choice with, select New choice. The New choice pane appears. 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 following screenshot. When completed, select Save:

      • Lead (under Value enter 1)

      • Opportunity (under Value enter 2)

      • Won (under Value enter 3)

      • Lost (under Value enter 4)

    Screenshot of new choice pane showing four choices entered.

  11. Once you've saved your New choice, select the drop-down list under Sync this choice with and find/select Prospect Stage.

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

  12. Under Default choice select Lead.

  13. Select Save.

  14. Select New > Column to add a new column.

  15. Enter the following information in the New column panel and then select Save.

    • Display name: Contract Amount

    • Data type: Currency

  16. Select New > Column 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.)

  17. Next, we're going to add a column with a behavior. This one is a little more complex. Add a column with the following information.

    • Display name: Forecasted Revenue

    • Data type: Formula

    Notice that the Formula input field appears below the Data type field once you select Formula as the data type.

  18. Enter the following formula into the field.

    Decimal('Contract Amount') * (Probability / 100)

    If you input the formula manually, notice how as you add the pieces, Power Apps auto-suggests possible fields/functions. In this case, we have to use the Decimal function to translate the Contract amount field from currency to a decimal number that our formula can use.

  19. Select Save.

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

    Screenshot of new business rule browser window.

  3. Select the Condition New Condition step inside of your design canvas. The Properties panel shows on the right hand side of the screen.

  4. In the Properties pane under Rules, look at your Field, ensure that Contract Amount is selected.

  5. For the Operator, change the value from Equals to Contains data.

  6. Select Apply.

  7. Now select the Components tab from the top of the same panel.

  8. Find the component Set Business Required and drag and drop it on the plus symbol to the right of the purple checkbox in the design pane (refer to the image below; the plus symbol appears as you begin to move the component to the canvas).

    Screenshot of drag set business required action to new condition.

  9. With Set Business Required New Action selected/highlighted, look at the Properties panel on the right and under Business Required, select Probability in the top dropdown and Business Required in the bottom dropdown.

  10. Select Apply.

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

  11. In the command bar at the top left of the screen, select the chevron just to the right of Prospect New business rule to expand the business rule name/description inputs.

  12. In the Business rule name, type Make Probability Required.

  13. Select Save from the command bar on the right.

  14. Select Activate to activate the rule.

  15. After a few moments, a popup window appears for Process Activate Confirmation. Select Activate in the popup to confirm activation.

  16. Close the browser tab.

  17. Select Done in the Currently creating a new Business rule pop-up.

  18. Notice your new business rule has been added to the Business rules screen. Now return to your table editing screen.

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

Import data from an Excel file

You use the spreadsheet named "Prospects.csv" for this exercise. Open this link and Download the file.

  1. Extract the Prospects.csv from the zip file.

  2. Open the Prospects.csv file in Excel. Notice the Stage column is empty. Let's enter these values manually. Enter the following values in the Stage column:

    • Contoso Flooring: Won

    • Fabrikam Inc: Won

    • Adventure Works: Lead

    • VanArsdel: Lost

    • Adatum: Lead

    • Relecloud: Opportunity

  3. Save your file locally as an Excel Workbook (Prospects.xlsx). Then close the spreadsheet.

  4. Return to your Prospect table in the Power Apps maker portal.

  5. Select the Import button at the top of the screen and select Import > Import data.

    Note

    Do not select Import data from Excel, which is a legacy function. Be sure to use the Import data which activates Power Query.

  6. In the Power Query popup, there are many ways you can point towards your Excel sheet. You can drag and drop the file, browse for it, or connect to it directly from OneDrive. Find the Excel file you saved locally and add it.

    Screenshot of Power Query window Choose data source showing the various options to connect to your data.

    Once you find the file (you might need to enter your connection credentials), ensure that the connection is valid and then select Next to continue.

  7. The Preview file data screen appears with your data. You should see four columns depicting ContractAmount, Probability, Name, and Stage. Select Next to continue.

  8. The Power Query editor opens to allow you to transform data prior to importing. Notice how Power Query automatically makes a guess at the type of data in each column. ContractAmount and Probability are defined as whole number, and Name and Stage are defined as text. In this case, leave it as it is. Select Next to continue.

    Screenshot of Power Query window showing data shaping options.

  9. Power Query switches to the Map tables screen, where you can select where you want the data to load to (either a new table or an existing table) and which columns in your existing data source that you send your Excel table data to. In this case select Load to existing table under Load settings, then select your Prospect table from the dropdown under Destination table. Completing this brings up the Column Mapping section showing the Destination column name from your selected table and the Source column with some dropdowns.

    Screenshot of Power Query window showing load settings and column mapping with load to existing table.

  10. Under Column mapping, select the Auto map button at the top right to let Power Query try determining how your columns match. Notice how the Power Query matches three of four of your columns automatically! Let's pick the last one manually matching Name to your ProspectName Dataverse column. Your mapping should now resemble this image:

    Screenshot of Power Query window showing load settings and column mapping with load to existing table and matched columns.

    Select Next to continue.

  11. Leave the Refresh settings to Refresh manually, and select the Publish button at the bottom right of the screen.

  12. The imported data doesn't appear right away, so go ahead and refresh your browser screen to show the data.

  13. Ensure that the data has been successfully imported. Your columns and data should look similar to this screenshot:

    Final look at columns showing imported data

Congratulations! You've created a custom table with a business rule and learned how to import external data into Dataverse.