How to import data from Excel to Dataverse with Lookups using Flow?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi all, I'm trying to create a flow to enable a school to import data from Excel into Dataverse.

I'm stuck on Lookup fields. For example I have a GroupType table referenced by a Group table.

My approach

I import the data from the groupTypes Excel file into Dataverse which generates the UIDs.

I then read data from dataverse into an array comprising groupTypeDescription and grouptypeid.

Challenge

When I import Groups I want to be able to pass the groupTypeDescription from the groups spreadsheet column and return the grouptypeid from the array and then use that to populate the lookup field on the groups Dataverse table.

The 'variables()' expression looks a likely candidate but I can't seem to crack it.

There are similar posts on here but I can't find the answer.

Can anyone offer any advice please?

Many thanks,

Jim

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous Using Data Flows and Power Query maybe a better solution for your requirements.

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous,

You don't need to populate an array of group description and group type. Instead to simplify the flow, use the "List Rows" action to get the required group ID and then use the group ID directly in your "Add a row" action.

Remember to bind a lookup field that format is: /PluralPhysicalTableName(guid). Example:

/cr23s_grouptypes(98bd54a1-2c1b-4e5a-bbb5-2b7b292d5119)

Another trick as well is with alternate keys. Instead of retrieving the group ID, if the description is unique, set an alt key on it and then just set the lookup. Example:

/cr23s_grouptypes(cr23s_alternatekeyname='descriptionfromexcel')

Dataflows is another good option as stated by @CNT if you need to run under a regular interval, but Power Automate is also a viable option where you have more control and can get a more performant process.

Hope this helps!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

A great many thanks CNT and EricRenier for your help here. I'm up and running with a BUT 😉

I had previously used dataflows but wanted more control as you identified.

List rows and an expression referring to the output was a solution BUT I'd love to implement the alternate keys approach which seems cleaner and more concise however I just can't get this to work.

The flow returns Error in query syntax. Any idea what I'm doing wrong below?

The lookup table plural name is hdc_reggrouptypes, the key is hdc_keyGroupType. I've tried all lowercase also -

FYI, I used the list rows approach to retrieve the regOccurrence key above 🙂

Cheers,

Jim

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I think you're missing a "/" before hdc_reggrouptypes

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi, good spot but I tried with a / before and same error unfortunatley

The regOccurrence value that uses list rows also does not include a / and works so I don't think it's required Eric

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Not sure if you solved it finally, but the other thing could be the case on the key name (ie hdc_keygrouptype)?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Eric I'm happy with the List Rows solution referencing the output as first(...) which is working well

Since then, I do now have the /xxx_odatapluraltablename(xxx_alternatekeyname='SearchText') approach working in other solutions.

One problem is that the prefix the system uses can sometimes differ from the 'Plural Display Name' displayed when you create or edit a table and exposed in odata. For example 'Plural Display Name: xxx_groups', actual plural name 'xxx_groupses' !

My solution To retrieve the right name every time I create a List Rows action on the table in question, run it, and then look at the odata string in the raw inputs 😉

thanks again for your excellent steer here, Jim 👍

Was this reply helpful? Yes No