cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimMeldrum1
Helper II
Helper II

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
EricRegnier
Super User II
Super User II

Hi @JimMeldrum1,

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!

View solution in original post

7 REPLIES 7
CNT
Community Champion
Community Champion

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

EricRegnier
Super User II
Super User II

Hi @JimMeldrum1,

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!

View solution in original post

JimMeldrum1
Helper II
Helper II

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 -

 

syntax.pngflowError.png

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

 

Cheers,

 

Jim

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

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

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

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 👍

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,693)