Please enable JavaScript.

Coggle requires JavaScript to display documents.

Importing Data, Use sharepoint document, Understand the source, Power…

- - - - Static Worksheet: A simple, one-time copy. What you see is what you get.
      - Dynamic Worksheet/PivotTable: A "magic" copy. You get an Excel file that is connected to Dataverse. You can hit the "Refresh" button in Excel tomorrow, and it will pull in the very latest data.
      - Open in Excel Online: This is for editing live. we can Add, edit and delete data. It opens your data in Excel on the web, and any changes you make can be saved directly back into the system.
    - - Word templates are usually for a single record (e.g., one quote for one customer)
      - Excel templates can be used to export a list of records into a pre-formatted spreadsheet with charts and formulas.
      - When creating the Word template, the user can select columns from the starting table ( Account main table and contacts related table) and columns from tables associated with the starting table by using direct relationships.
      - Email templates
    - - Important Note: This method gives you the raw data. Lookup columns will just be confusing IDs (GUIDs), and choice columns will just be numbers, not the text labels. It's meant for technical use, not for easy reading.
    - - Instead of just copying data occasionally, it creates a live, continuously updated mirror of your Dataverse data in a high-performance Azure Synapse environment. This means analysts and data scientists can query and process data efficiently — including both standard and custom tables — without affecting the original Dataverse system.
        This seamless, real-time replication makes it ideal for advanced analytics, reporting, and integration with other Azure services.
- - - - This method is the safest way because it guarantees your file has the exact structure the system needs, preventing common import errors.
- - - - Once you create a dataflow in the Power Apps portal, you can get data from it using the Dataverse connector.
        Dataflows are created and managed within a specific environment.
        You might need a data gateway to connect to on-premises data sources.
        The end result is a robust, automated way to bring external data into Dataverse and keep it up-to-date.
        
        Detailed Steps:
        Create a Dataflow: In Power Apps, navigate to Dataflows under the Data section. Click New dataflow.
        SharePoint Lists: Dataflows are a perfect tool for importing and regularly syncing data from a SharePoint List into a Dataverse table.
        
        Author the Dataflow (The "ETL" Process)
        This is where you build the pipeline.
        
        • Extract: Connect to your data source (e.g., a SharePoint list).
        
        • Transform Data: The Power Query editor will launch. This is where you can clean, filter, reshape your data before it gets loaded.and transform the data from your source. You can remove columns, change data types, split columns, and perform various other transformations to get the data into the desired format before it's loaded into Dataverse. For example, you can remove unneeded columns, change data types, or merge columns.
        
        • Load: Map to Dataverse Table: After transforming, you will be prompted to choose a target Dataverse table to load the data into. You can either map to an existing table or create a new one.Map the columns from your source to a Dataverse table. You can load the data into a new table or an existing one. You must define a primary key to avoid duplicate rows.
        
        Schedule dataflow runs / Schedule the Refresh / configure
        This determines how often your data is updated.
        You can set the dataflow to refresh manually whenever you choose.
        Or, you can schedule it to refresh automatically at a set frequency (e.g., daily, weekly hourly) to keep the data in Dataverse current.
        
        Choose the Data Source: Select your data source. This is where you can connect to SharePoint Online List for importing list data. You can also connect to Excel files on SharePoint.
        
        Use the data you loaded to the destination storage. You can build apps, flows, Power BI reports, and dashboards. Or connect directly to the dataflow’s Common Data Model folder in your organization’s lake using Azure data services like Azure Data Factory. Or you can use Azure Databricks or any other service that supports the Common Data Model folder standard.
  - - - Lookup Field Matching: Missing lookup value doesn’t result in failure by default. If an imported lookup value (like “Company”) doesn’t match an existing record, Dataverse will typically leave the lookup empty—it won’t cause an import failure, unless additional rules or flows interfere. No documented behavior indicates auto-creation or error by default.
      - Required Fields: Business-required fields must be populated; missing them causes error.
      - Option Set (Choice) Values: If the import includes a value not defined in the option set, it will error. (Not directly cited, but generally consistent behavior in Dataverse.)
      - Duplicate Detection Default behavior: Duplicate detection is off by default Only core entities like Accounts and Contacts have built-in duplicate detection rules out-of-the-box. Custom or other standard tables do not automatically detect duplicates unless custom rules are set up.
        Unless duplicate detection is enabled, duplicates are allowed by default.→ allowed unless duplicate detection is configured.
        
        Duplicate of earlier row in same file- it won't throw error because Invalid only if duplicate detection is enabled.
      - Yes/No (Boolean) Fields: Prompts you to map incoming values (e.g., "Yes" to "Allow").
        Trick: Yes/No is a Yawn, you'll be asked to Yield a mapping. Boolean fields allow mapping from text values “Yes”/“No.”
      - Company matches two accounts (ambiguous lookup)
        Throws error or Import fails due to ambiguity—system cannot resolve duplicate lookups. This aligns with expected behavior.
      - Text Field Lengths: Text field length extends automatically to fit longest imported value (admin only), no warning.
        Trick: Text loves to Stretch.
        Default max length is 100 characters for lastname
      - Email Address Validation: Email format is NOT validated; any text is imported.
        Trick: Email is Easy-going – No Exact format check.
        Basic validation: By default, Dataverse does not validate email format during data import.
        Smart email validation (Preview feature): Available for Dataverse text columns set to “Email” format in model-driven apps, but this is disabled by default and must be explicitly enabled. Even then, it does not block saving, only highlights issues.
      - the Data Import Wizard — it’s a built-in feature of Dataverse.
      - You access it from:
      - Power Apps (make.powerapps.com) → Select environment → Dataverse Tables → Import Data
      - or from Power Platform Admin Center > Environments > [Environment] > Settings > Data management > Imports.
      - Advantages (based on Microsoft docs)
        Choice (Option Set) values can be mapped by label, not by integer value.
        Lookup fields can be mapped by name, not GUID.
        Can import related records (e.g., Contacts with Accounts).
        Can save Data Maps for repeated imports.
      - Limitations
        Performance: Best for small data imports — not suitable for bulk loads.It can import many rows (thousands), but it’s slow for large datasets. Microsoft recommends ≤ 20,000 rows per file for reliability. it can handle thousands, but performance drops significantly at scale.)
        No data transformation: Data must already be clean and match Dataverse requirements.
        Not for scheduled/automated loads — one-time or ad hoc use only.
        For large/bulk/recurring imports → use Dataflows, Power Query, or Azure Data Factory instead.
    - - Where:
        
        Power Apps (make.powerapps.com) → Select environment → Dataverse Tables → Import Data
        
        Power Platform Admin Center > Environments > [Environment] > Settings > Data management > Imports.
        
        How/Steps:
        Prepare file (headers = columns).
        Start Import (Get Data).
        Map file columns to table columns.
        Save mapping as Data Map (for reuse).
        Finish import.
- - - - Check for Existing Record: Before creating a new record, use a List rows action with a filter query to check if the record already exists in Dataverse. This prevents duplicate entries.
        
        Create or Update: Use a condition to check if the record exists. If it does, use the Update a row action. If it doesn't, use the Add a new row action.
        
        Map Columns: Map the columns from your source (e.g., SharePoint List fields) to the target columns in the Dataverse table.
- - - - Export Data: Use the schema file to export the configuration data from your source environment. This generates a .zip file containing the data.
        
        Import Data: Connect the tool to your target environment and use the .zip file to import the data.
        
        Use Case: This tool is specifically designed for moving static data, such as security roles, business units, or configuration settings. It is not for transactional data like Accounts, Contacts, or custom records that are frequently created by users.
- - - - Document Migration (SharePoint → Dataverse)
        Detailed Steps:
        
        Prepare SharePoint: Ensure files have an ID column (e.g., AccountId).
        Ensure that the documents you want to migrate have a clear link back to the Dataverse record. This is typically done by storing a column in SharePoint that contains the unique ID (GUID) of the related Dataverse record, such as the Account GUID or Contact GUID.
        
        Create a Cloud Flow: In Power Automate, create a new automated or instant cloud flow
        
        Iterate through Documents: Use an action like Get files (properties only) from the SharePoint connector. This will retrieve a list of all files from your library.
        
        Find the Dataverse Record: Use a Get a row by ID or List rows action in Dataverse to find the corresponding record using the ID you stored in the SharePoint column. Use a filter query to make this efficient.
        
        Create a Note (Annotation): Use the Dataverse connector's Add a new row action, selecting the Notes table. This is the official way to attach documents to records in Dataverse. Set the Regarding field to the Dataverse record you found in the previous step.
        
        Attach the File to note: Use the SharePoint get file content action to get the document content. Then, in the Notes table creation step, populate the Document and File Name columns with the dynamic content from the SharePoint connector.
- - - - Think of templates as a "mail merge." You create the form, and Dataverse fills it in for you.
        Templates are a key part of solutions. They are not raw files but managed components that are deployed along with the rest of your app. This makes them easy to move from a development environment to a production environment.
        
        Detailed Steps:
        
        Create a Sample Template: In the maker portal, go to your solution or the main Power Apps screen. Navigate to Templates under the Advanced Settings area. Select the type of template (Word, Excel, or Email) and click Download template.
        
        Design the Template: Open the downloaded template.
        For Word/Excel: Use the Developer tab to insert Content Controls (the placeholders that will be filled with data). Map these content controls to the fields of your Dataverse table.
        
        For Email: Design the email content. You can insert dynamic fields using the in-app editor.
        
        Upload the Template: Once your design is complete, save the file and upload it back into Dataverse from the same Templates area.
        
        Add to Solution: To make the template part of your application and portable between environments, add it to your solution as a Solution Component. This is a crucial step for development lifecycle management.