Answered

Dataverse Edit in Excel - Issue with Lookup Columns in Excel

(1) Share

Report

Posted on by SteveKane

I have a Dataverse table that has a few Lookup columns to other Dataverse tables. I'm trying to seed some fake data into the table for a demo, so I'm editing the table in Excel.

When I try to set the value of the lookup column, it works fine if I highlight a blank cell and then select value ("Team1") from the right-side Data Connector pane. But say I want that same "Team1" Lookup value in the next six rows. If I copy the value of the cell and paste it into the next cell below, sometimes it seems to be linked to right-side list, sometimes it does not and is just plain text. Below is an example. The first row, I select Team1 from the right-side list. I then highlight that cell, copy, and paste it down five more times. The first five rows show that they are linked to the table, but seemingly randomly, the sixth cell is not linked. If I then try to Publish the data back to Dataverse, that cell will go blank, and the data for that row is not published.

Any ideas for an easy way to enter a couple hundred or so lines of data in this Excel file, with several lookup columns, working around this problem without having to click in the Data Connector pane hundreds of times?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (11)

Answers (1)

AhmedSalih 6,680 Moderator on at

Like (0)

Report
Copy link

Link copied!

I would export the Custom Table to Excel. It will be exported with the actual value of the Lookup Column e.g. Team ID#. Then you can fill the records you want with whatever Lookup value you need. Make sure that your 1 table (the table that your lookup column is looking to) has a Key (Primary Key) in your case it could be the Team ID. From dataverse, load data into the custom N table from the Excel file that you exported, and you then map your fields (This will create DataFlow). One thing is if you don't have a none GUID primary key in your N table, delete all the records from the N table before uploading the new data to avoid duplicates.

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi @SteveKane ,

I do this a lot for customers where I need to add test data and even with migrations. My normal approach is the following, hopefully this helps:

Go to admin.powerplatform.com

From navigation choose "Environments" and then choose your environment from the list on the right

Select Settings from navigation on that environment

Choose Templates and the "Data Import Templates"

Select your table and download your template

This is your starting point to import the data to your table...

I use this and populate the data needed to create the test records

All lookups fields I fill in with the "Primary Name" field value (must be unique or it will not import the record because it will see a duplicate name

After populating the information I will then use the Data Management --> Imports to import the data.

That is basically it. I use this approach to upload thousands of records across many tables for environments I build. It takes getting comfortable in Excel and the process but works GREAT! I know there are plenty of other methods but this is the one that I have grown accustomed to and works well.

Was this reply helpful? Yes No
AhmedSalih 6,680 Moderator on at

Like (0)

Report
Copy link

Link copied!

@dpoggemann , Thank you for this tip. I have a question, you said "All lookups fields I fill in with the "Primary Name" field value (must be unique or it will not import the record because it will see a duplicate name", you mean the values in the ONE table should be unique, right? Because if I am importing data to the N table in the relationship, the lookup values will certainly wont be unique in my case.

Also, If I need to regularly sync data from Excel to the dataverse until the development is completed, should I use DataFlows or the method you mentioned as of which is considered to be the best practice.

One more thing I noticed after I tested your method, the template didn't include the custom optional columns, only the required ones! do you know why is that?

Thank you

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @AhmedSalih ,

Correct. The table with the lookup column can not have two "Primary Name" values that are duplicate or when the process runs it will see it as a duplicate and not insert since it doesn't know what row to match with in the ONE side of the relationship... If this is the case and there are duplicates but the table has another unique column, you can setup custom mappings through the Data Import Wizard as well...

I don't think there is a "best practice" here... It depends on your business situation. Dataflows are great as well but normally I am not keeping data in sync but populating specific information in my dev and test environments. If you are running regularly and syncing then Data Flows would be a better approach in my opinion.

Was this reply helpful? Yes No
SteveKane 38 on at

Like (0)

Report
Copy link

Link copied!

Thanks, @dpoggemann. That's 90% of what I need. I just did a quick test, and it works well with lookup columns.

I find it odd, however, that the exported template only shows Required columns, not Optional or Recommended, as @AhmedSalih noted. To verify this, I have a test table and created three test columns, "TextRequired", "TextRecommended", and "TextOption", with the Required attribute set accordingly on each. The exported template only shows "TextRequired". Not sure why this method doesn't include the non-required columns...maybe worth a feature request?

I guess as a second step I can do the "Edit data in Excel" method to add data to the non-required columns....at least I won't have the fear of hitting the Publish button and having a bunch of my hard work disappear because the lookup columns didn't validate. Been there done that...   😮

The article below references your method, as well as "bringing my own source file" and mapping fields. I'll maybe play with latter in the future, but this'll get me going with my current needs (and schedule).

https://docs.microsoft.com/en-us/powerapps/maker/data-platform/data-platform-import-export

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @SteveKane ,

Whenever I export the template for the table from the "Data Import Templates" it should include all columns on the table basically (optional and required). I just validated this with a table of mine and that is what it provides. Are you exporting the template using this methodology?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

@SteveKane

I should say all the columns you would want to set, not standard columns that are used behind the scenes used by system, not set by users...

Was this reply helpful? Yes No
SteveKane 38 on at

Like (0)

Report
Copy link

Link copied!

Below is a screen shot of my test table structure, and the resulting Excel import template, which is missing the Optional and Recommended columns. The hidden A, B, and C columns are the "DoNotModify" columns and there's nothing else to the right. Not sure why you show all columns. I'll Bingle this some more and see if there is some hidden option.

Was this reply helpful? Yes No
SteveKane 38 on at

Like (2)

Report
Copy link

Link copied!

Okay, I figured it out. 😊

Apparently, the Data Export template relies on the Main Form of the table. I edited the Main Form, added the TextRequired, TextRecommended, and TextOptional fields to the form, Saved, Published. Viola, the exported Excel template has all the available fields. Just to reconfirm, I removed the fields from the form, and saw the columns were missing again from Excel...re-added them, and columns came back. Proof below.

Was this reply helpful? Yes No
AhmedSalih 6,680 Moderator on at

Like (1)

Report
Copy link

Link copied!

@SteveKane , Dataverse is a great data source for Powerapps, but man, it is confusing in too many aspects.

Was this reply helpful? Yes No