cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Import data into a lookup

Ok I am looking for some suggestions on how to convert my data for a lookup in CDS from an old Sharepoint Access App. I have a Job Sheet table/entity and I am trying to transfer all the data from my Access App to my PowerApp. I have a couple of lookup fields in the table/entity which I need to convert across. The data when I downoad it from the Access App to Excel has the actual values (the address field has the actual address and not the ID) but the data to go in needs to have the ID and not the actual address so it can look it up in the address table. For most of the lookup fields this is easy as there are only about 8 different possibilities and a quick find and replace will fix that. But for the address field there are over 2000 addresses. I have a seperate Excel spreadsheet which has all the addresses and ID's in. 

 

Can anyone suggest a simpler way to change all my addresses to the ID without having to do it on an individual basis.

 

Any help is really appreciated!

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Import data into a lookup

Could you please explain a bit for what the data source you used here?

And the field that you would like to work with?

If the Excel table contains all the Addresses and the related ID, then we could take use of Lookup function to find the proper ID with the Address Mapping.

If the Address name is the same as the one you would like to replace with, then please rename the Address field in Excel to Address1 or somthing else,

Then to replace the Address field, the formula should be:

Forall(ExcelTable,

           Patch(CDSTable, Lookup(CDSTable, Address=Address1), {Address:ID})
      )

Besides, if you are working with Lookup Field in CDS, then the formula might be a little different.

Take a look at the example below:

Patch Lookup field in CDS

 

Regards,

Michael

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Import data into a lookup

Could you please explain a bit for what the data source you used here?

And the field that you would like to work with?

If the Excel table contains all the Addresses and the related ID, then we could take use of Lookup function to find the proper ID with the Address Mapping.

If the Address name is the same as the one you would like to replace with, then please rename the Address field in Excel to Address1 or somthing else,

Then to replace the Address field, the formula should be:

Forall(ExcelTable,

           Patch(CDSTable, Lookup(CDSTable, Address=Address1), {Address:ID})
      )

Besides, if you are working with Lookup Field in CDS, then the formula might be a little different.

Take a look at the example below:

Patch Lookup field in CDS

 

Regards,

Michael

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Import data into a lookup

I was working on the data in an Excel spreadsheet before I import the data into the Entities. I managed to sort it with an update query in MS Access.

 

Thanks for the help.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,658)