Bulk upload data to data verse table which includes lookup columns

(1) Share

Report

Posted on by Jap11

453

Hello,

I'm very new to using dataverse so hoping for some guidance.

I have my dataverse table connected to my power app. I'm now at a point where I want to bulk upload all my data. Some of my columns are lookup columns, they were created this way as when looking online this was suggested to create “Taxonomy” table with Parent options for all my different category, object type, and sub type drop down options.

I'm now struggling to bulk upload to these columns. I have tried data flow however the columns do not appear to map the column to. I have also tried editing the data verse table in excel and using the power app plugin and simply pasting the values however when i select publish, they disappear.

I have almost 8000 rows of data so manually selecting the category, object type, and sub type isn't an option.

I seem to be going round in circles, so wondering if there is a way to do this or a work around?

Any help appreciated.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Jap11

In the first place let's see if this helps to address your concern.

IF not, we are in a process to follow!

With look up columns and its value, to do a bulk upload into Dataverse tables bit tricky. To be honest its not in Dataverse, it's in every database. A first routine must be your parent table aka look up table must be populated first before doing the full upload. It is obvious lookup columns require referencing related records by their unique identifiers (GUIDs), not just display names or text values.

One initial way can be, if we can make ready lookup data with GUIDs will help. To do so, export your taxonomy(lookup) table with both display name and GUIDS. In the bulk data file, let's replace lookup column values (e.g category names) with the relevant GUIDS from the taxonomy table.

And then we have Dataflows to use Power Query with the lookup GUIDS. in PQ merge bulk data with the the taxonomy table on the display name to get GUIDS kind of to do a mapping. What you can do here is map the look up columns in Dataflows to the respective GUID columns. This affirms DF imports the correct GUIDs for lookup columns.

The third way can be "Power Automate" way - you can build a PA flow that reads your bulk data (assuming bulk data is in drive). Then for each bulk row look up the taxonomy guid by name and then create or update the record with the right lookup GUID. This approach is tedious but makes sure mapping is correct. Please note that if you follow this step, I would recommend do a small chunk test firts.

The fourth way is the "Dataverse" way: we can use excel online with dataverse Add-in. Here, export the template from DV with lookup column included. The tricky part here is, fill the lookup column with GUIDs not with names. Paste your data and publish. Be bit prudent here, if you only have names, you must first convert them to GUIDs externally.

Please let me know if these steps help to address the issue.

Was this reply helpful? Yes No
Jap11 453 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Haque - thank you very much for your help.

I actually tried using the GUIDs for mapping but i was putting them into the wrong column. Once i populated my column which the GUIDs and then populated my lookup column with the actual corresponding category type i.e. merchandise, i was able to publish successfully.

Was this reply helpful? Yes No