With the emergence of digital transformation, firms are continuously looking for more efficient ways to manage and use their data for app development. One key step in this direction is the combination of SharePoint Lists and Microsoft Dataverse, which heralds a new age in enterprise app development.
This integration makes it easier to convert from traditional SharePoint Lists to more powerful, scalable Dataverse Tables that are Copilot compatible with Canvas Power Apps generation. It takes advantage of Microsoft’s cloud capabilities to create a coherent and streamlined experience for designing enterprise-level apps.
Users may now easily import SharePoint List data into Dataverse tables using the ‘Create with SharePoint List’ option in Power Apps and develop Power Apps using Copilot.
In this article, we will walk you through the businesses in the Power Apps process to help you understand how to import SharePoint lists into Dataverse tables and Power Apps.
Enterprise App development needs
Are you a novice or a regular user of Microsoft Dynamics 365? Regardless of your expertise level, you will always need some expert guidance to understand licensing. It can be complicated, and with every update, it is hard to go through a long document when you only need licensing information for one application.
To help you navigate through this complex topic, we have answered your question about what license I need. We will focus on basic information on licensing types and the Dynamics business application licensing requirements.
Ready for a thorough analysis? Let’s start with the Dynamics 365 licensing guide!
Functional
- Powerful built-in AI and analytics
- Advanced business logic and eventing
- Built-in data modelling
- Deep integration with Dynamics 365 and other apps.
Non-Functional:
- Comprehensive security
- Planet scale data platform
Understanding Dataverse and its Features
Dataverse’s standard and bespoke tables offer secure cloud storage for your data. Tables enable you to construct a business-focused description of your organization’s data for usage in apps. The major features it offers to enterprises are:
Dataverse provides a comprehensive security strategy that protects users’ data integrity and privacy while enabling efficient data access and collaboration. It allows complete user access by combining business units, role-based security, row-based security, and column-based security.
Tables in Dataverse can benefit from extensive server-side logic and validation to assure data quality and eliminate repetitious code in each app that makes and utilizes data from a table.
Unify data management and access the visual representation of each data, turning it into fine reporting for further development of the enterprise accordingly.
Both the metadata and data are stored in the cloud. You don’t need to worry about the details of how they’re stored. You can search relevant data, go for relational data, and log files as needed.
Dataverse integrates data from numerous sources, including Azure, GitHub, webhooks, ALM, and data export, into a single store. It may then be used in Power Apps, Power Automate, and Power BI alongside existing Dynamics 365 data.
Microsoft Dataverse or SharePoint Lists
We will share a simple comparison to help you understand both equally. The following is an image comparing the features of both. Some points, like relational databases and rich data types, make us wonder about our choice for SharePoint. So, is it better to use Dataverse?
Now, to answer the question, the choice is clear from the following table. Sharepoint is missing a lot of features in it. Therefore, we suggest building on Dataverse.
How to import SharePoint data into Dataverse and build PowerApps with Copilot
As we step into the process, here is our SharePoint list containing all the information about it, including description, status, start date, and end date. Now that we know the list, the question is how to import it to Dataverse. This is why we are here. You have two options. One is to open Power Apps, go to “Tables,” and select “Create With Sharepoint List” to get the table import started.
However, the more sophisticated way, and the one powered by Copilot, is to open Power apps, and you will see “start with data”.
Select an already-existing SharePoint connection or create a new connection to the SharePoint account of your choice.
Next, type in the SharePoint URL you want to use, or choose one of your most recent websites and choose a list.
After that, you will see a preview of your table, which you can change and evaluate. To create a Dataverse table and responsive canvas app, select Create app.
When you click Create app, we will create a Dataverse table based on the table configuration you specified in the previous step and then generate a responsive canvas app utilizing it.
Depending on the quantity of your data, you may not view all of it on the Canvas app right away. This is because we want to speed up the building of your app by submitting the top 20 rows of data first. The remaining data will be uploaded in the background, and you can always check the status from the table hub or table designer.
Steps to transform your SharePoint lists into Dataverse tables
When you have multiple lists to import, you need a step-by-step process to get it right. Here are the lists with their tasks:
Identify the limitations of the initial import:
When you try to import a SharePoint list that contains a lookup column (which creates a relationship between two tables in Dataverse) and a person-type column “Assigned to” (where you can pick users from the active directory) using the “Create with SharePoint list” feature, Dataverse doesn’t recognize the lookup column and imports the person type column data as text instead of users.
Transform data using Power App:
To address this limitation, you need to use Power App, which allows you to clean and shape your data before loading it into Dataverse. To do this, you will navigate to the Power app. Go to “Tables” and then select “Create with SharePoint list”
- Select the columns you want to import from the SharePoint list.
- Extract the relevant information from the lookup and person type columns. For instance, from the lookup column, you will extract the title of the referenced item. From the person type column, you will extract the email address.
Following is the preview of the table.
It shows all the information, however, when you see the “lookup column”, it does not support the relationship between tables in Dataverse. Besides, in sharepoint, the ‘Assign to’ column is person type, but here it shows it as text. So, how to map it? Let’s see in the next steps.
Person Type Column Mapping:
In this step, we will go to the tables and select “Import data.” We will then select the SharePoint online list.
Add the credentials and SharePoint site URL and click “Next.”
We will look for the tasks and select them, and it will show all the data we will import. As we move next, it will land us in Power Query.
Transformations of the Data:
Now, select the columns you want to import. You can choose the ones you need; we will select priority, date, assign to, and project lookup.
You can see that the 2 columns come in the form of a record because they are completed-type columns.
Now, we need to take information from these columns, and therefore, we will take some additional steps to do it.
We will be creating a custom column. We will add the schema and call project lookup.
Click “ok”, and you will see the details.
We will perform similar steps for the “Assign to” column and call the information.
We do not need the record columns now; therefore, we will delete them.
Create a new table in Dataverse:
Now, we will create a new table in Dataverse with the appropriate schema to represent the data we want to import. This includes creating columns for the lookup column (set as a lookup data type) and the person type column (also set as a lookup data type).
Define relationships between tables:
It created the table, but we could not create the relationship between tables. In this case, we will define relationships between the newly created table and the tables that store project and user information. We will create a new table here with the name “Tasks”.
Save it and further add the information.
We can add the columns we want and set up the type of column we need. Once we have done this, we can create a new column, such as project lookup. We will add the name and data type and relate it to the table named Project in this example. It will list all our projects.
We replete similar steps to the ‘assign to’ column and relate it to the table “user”.
The next step is mapping the data.
Map the data during import:
Once you have transformed your data and created the table with the proper schema, you can use Power Query to map the data from the SharePoint list to the corresponding columns in the new Dataverse table.
When mapping the lookup and person type columns, you will use the extracted data (title for the lookup column and email address for the person type column) to ensure the relationships between tables are correctly established.
Now, we see that the Assign to the column is calling the active directory, which we do not have. For this, we need to create keys.
Creating Keys:
We will go back to Dataverse tables and look for our project table. Once we select it, we can see the keys button. Click it to create new keys.
As we were trying to map it to the title, so we will call it a title and save it.
As we refresh, the key is created, you can see this in the image below:
We will follow similar steps for the user table, create the key, and call it email for mapping.
Now, when we go back to the power Query tab and refresh it by going back, we can see the proper mapping as we did.
Once we set the fields and publish them, we will have new data flows.
Create a canvas app:
After successfully importing the data and defining the relationships, you can create a canvas app to visualize the data in Dataverse. The canvas app will allow you to view and interact with the data, including the relationships between projects and tasks.
Bottom line
Now, as we have created the app by importing data and transforming SharePoint lists to Dataverse tables, you can use similar steps to create your apps and import data accordingly. If you need guidance from a consultant, we at DHRP have the best team to help and guide you. Reach out to get your Dataverse tables right.
