Skip to main content

Notifications

Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : TT0/xOdxRTtc2cjFYLgNPv
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / Auto-generate ID when ...
Power Apps - Microsoft Dataverse
Answered

Auto-generate ID when import the excel/Sharepoint list to dataverse

Like (0) ShareShare
ReportReport
Posted on 8 Dec 2022 10:22:06 by mmchx 150

Hi All,

I'm working on the Dataverse for MS teams, and want to migrate the data from the Excel / SharePoint list to Dataverse.

I have an issue that when I imported data, it doesn't generate the ID like the SharePoint list. So, I tried creating the column with auto number type before importing the data. The result is it doesn't generate the auto number for me, it will generate it when I create the new row only.

 

How I can achieve that to import data with auto number generating? 

The purpose of auto number column is to create the unique id of each item like in the SharePoint list.

Categories:
Dataflows
Microsoft Dataverse
Tables
Solution Explorer
Entities
  • Az8PnG Profile Picture
    Az8PnG 85 on 31 Jul 2023 at 00:53:38
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    I see, understood! I already try this formula but it is not working on me.

     

    I am having difficulties in creating an auto increment which starts with prefix.

    In the picture below, I wanted to make if the user choose the radio button such as Electronic, the part number will increase 1, which is it will be "E-000005".

    Az8PnG_0-1690764731503.png

     

    I want to make it increment 1 based on the latest part number according to their Stock Type in database (as picture)

    Az8PnG_1-1690764731681.png

     

    This is my code for:

    1. OnChange radio button:

    UpdateContext({varSelectedStockType: Radio2.Selected.Value});
    UpdateContext({varPartNumber: LookUp(Sort(Filter(Samples,'Stock Type'= varSelectedStockType),'Part Number',SortOrder.Descending),true,'Part Number')});

     

    2. Default text input:

    Concatenate(Left(varSelectedStockType,1),"-",Text(Value(Right(varPartNumber,Len(varPartNumber)-2))+varCounter,"000000"))

     

    3. On Select Save button:

    SubmitForm(Form3);ResetForm(Form3);
    Set(varCounter, varCounter + 1)

     

    May I know is there any different?

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 Super User 2025 Season 1 on 29 Jul 2023 at 00:53:35
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    Hello, @Az8PnG, the import errors using the import method are not clear sometimes. Also, in the Dataflows, you can do full ETL which I do recommend preparing your data before the load. 

  • Az8PnG Profile Picture
    Az8PnG 85 on 28 Jul 2023 at 03:14:21
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    Hi @AhmedSalih May I know why did u import your excel using dataflow instead of manually clicking on "Import from Excel" button?

  • Verified answer
    AhmedSalih Profile Picture
    AhmedSalih 6,678 Super User 2025 Season 1 on 13 Dec 2022 at 16:52:17
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    @mmchx , That is correct!

  • mmchx Profile Picture
    mmchx 150 on 13 Dec 2022 at 16:48:53
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    appreciated it. seems like I need to create the new ID column(auto-number) on the dataverse, instead of using the Primary column as ID column (auto-number)

  • Verified answer
    AhmedSalih Profile Picture
    AhmedSalih 6,678 Super User 2025 Season 1 on 13 Dec 2022 at 16:34:06
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    @mmchx, I just ran a test. Here is what I did:

    I created this Excel File:

    AhmedSalih_0-1670949038754.png

     

    Then I created a DataFlow to load the Excel data into an Existing Dataverse Table.

    I created the Dataverse table with only one custom column (Auto-Number) 

    The seed was Test as Prefix and 1000. 

     

    Ran the DataFlow:

    The result:

    AhmedSalih_1-1670949158346.png

     

    I created the CC record in Dataverse. Records A to E were created via the Dataflow and the flow generated the ID value (of the AutoNumber Column) automatically.

     

     

    If my reply helped you, please give a đź‘Ť , & if it solved your issue, please đź‘Ť & Accept it as the Solution to help other community members find it more.


    I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.


    Visit my Blog: www.powerplatformplace.com


     

     

     

     

     

  • mmchx Profile Picture
    mmchx 150 on 13 Dec 2022 at 15:36:56
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    Actually, both of them.

    First scenario

    1. doing the migration process first which needs dataverse auto-generate the number when migrating the data.

    2. User who can add/edit data to dataverse, can add record and dataverse needs to auto-generate the number after the last record from the migration process. but the issue occurs when a user added the record after the migration process. It doesn't auto-generate numbers correctly as I mentioned.

    For example

    I migrated data(Excel file) and created column "ID" in the excel file (HC-0001 to HC-0030), after the user added the new record directly to dataverse. the expected result is dataverse auto-generate number to HC-0031 but the dataverse generated HC-0001 instead.

    mmchx_0-1670945457636.png

    Second scenario

    1. I manually created 30 records directly to dataverse (HC-0001 to HC-0030)via Power app Excel add-in. (Dataverse auto-generated number correctly).

    2. User would like to import the data to the dataverse via data flow by using the Excel file. which the user has already created the ID column and created the seed number in the excel file. (This one is in case the user has multiple records and they don't like to copy-paste.)

    For example

    I manually created on the dataverse -> the auto-number are HC-0001 to HC-0030, then the user imported 2 record from the Excel file to dataverse and already defined the seed number which is HC-0031 & HC-0032. Once imported completely, Ok it shows HC-0031 & HC-0032. But if the user created the record (directly on dataverse) after importing it auto-generated HC-0031 & HC-0032  so, this will be 2 records that have ID: HC-0031 and 2 records that have HC-0032.

     

     

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 Super User 2025 Season 1 on 13 Dec 2022 at 14:52:18
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    @mmchx, You Load the Excel data first, then your seed start one number after the last record in Excel. Excel should be HC-0001 to HC-0030 and then create those in Dataverse. Is this a migration process or will be ongoing integration?

     

  • mmchx Profile Picture
    mmchx 150 on 13 Dec 2022 at 14:14:26
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    Could you please show me some examples? 

    As I tried, Created 30 records on the dataverse first and my auto-number was HC-0001 to HC-0030 then I imported 2 records with ID HC-0031 & HC-0032 (created on the excel file).

    It mapped to the auto-number column correctly, but when I created 1 record directly on the dataverse. It generated the auto-number as HC-0031.

     

    do I do something wrong or do I skip some steps? 

     

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 Super User 2025 Season 1 on 08 Dec 2022 at 15:16:02
    Re: Auto-generate ID when import the excel/Sharepoint list to dataverse

    Hello, @mmchx

     

    Create an ID Column in the Excel file and then map that column to your Dataverse auto-number column. Make sure that ID column has the sequence of numbers from 1-whatever the last record number is. For the Auto-number column in Dataverse, make sure that the seed value is one number after your Excel last records ID value. So when you create new record after the import, it will start after the last ID.

     

     

    If my reply helped you, please give a đź‘Ť , & if it solved your issue, please đź‘Ť & Accept it as the Solution to help other community members find it more.


    I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.


    Visit my Blog: www.powerplatformplace.com


     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,668 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,004 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans