Answered

Import data into a lookup

(0) Share

Report

Posted on by SupportForce

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

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

v-micsh-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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
SupportForce 45 on at

Like
a
(0)

Report
Copy link

Link copied!

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.