Exercise - Create a custom table and import data
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
Go to the Power Apps maker portal and sign in (if necessary).
On the menu, select Tables.
Select New table and select Set advanced properties.
Under Properties, enter the Display name: Prospect.
Select the Primary column tab and change the Display name to Prospect Name.
Select Save.
After a few moments, the Tables > Prospect screen appears.
Select New > Column to add a new column.
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
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)
Once you've saved your New choice, select the drop-down list under Sync this choice with and find/select Prospect Stage.
Under Default choice select Lead.
Select Save.
Select New > Column to add a new column.
Enter the following information in the New column panel and then select Save.
Display name: Contract Amount
Data type: Currency
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.)
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.
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 autosuggests 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.
Select Save.
Add a business rule
In the table designer, find the Customizations pane on the far right and select Business rules.
Select New business rule at the top of the screen; a new browser tab opens.
Select the Condition New Condition step inside of your design canvas. The Properties panel shows on the right hand side of the screen.
In the Properties pane under Rules, look at your Field, ensure that Contract Amount is selected.
For the Operator, change the value from Equals to Contains data.
Select Apply.
Now select the Components tab from the top of the same panel.
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).
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.
Select Apply.
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.
In the Business rule name, type Make Probability Required.
Select Save from the command bar on the right.
Select Activate to activate the rule.
After a few moments, a popup window appears for Process Activate Confirmation. Select Activate in the popup to confirm activation.
Close the browser tab.
Select Done in the Currently creating a new Business rule pop-up.
Notice your new business rule has been added to the Business rules screen. Now return to your table editing screen.
Import data from an Excel file
You use the spreadsheet named "Prospects.csv" for this exercise. Open this link and Download the file.
Extract the Prospects.csv from the zip file.
Open the Prospects.csv file in Excel. Notice the Stage column is empty. You need to 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
Save your file locally as an Excel Workbook (Prospects.xlsx). Then close the spreadsheet.
Return to your Prospect table in the Power Apps maker portal.
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.
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.
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.
The Preview file data screen appears with your data. You should see four columns depicting ContractAmount, Probability, Name, and Stage. Select Next to continue.
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.
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.
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:
Select Next to continue.
Leave the Refresh settings to Refresh manually, and select the Publish button at the bottom right of the screen.
The imported data doesn't appear right away, so go ahead and refresh your browser screen to show the data.
Ensure that the data has been successfully imported. Your columns and data should look similar to this screenshot:
Congratulations! You've created a custom table with a business rule and learned how to import external data into Dataverse.