cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AFWright
Level: Powered On

Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

I have a large catalog of products that is stored in SQL that is used throught the app. However, I need to store some of the data for these products in an Excel table to make changes easier and quicker for the support folks. 

 

I'm finding that only the top of the list (may be 500 or so items I would guess) can be referenced using the LookUp function. I'm using a unique id (not duplicated in the target Excel table to reference with the following formula: 

 

LookUp(Catalog_1,  ItemCode in Gallery.Selected.Item, ItemImage)

 

Does anyone have experience with this issue?

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

@AFWright 

 

I haven't done extensive testing, but for a static table of information that doesn't require frequent edits Importing the static Info from Excel can be a good choice. It doesn't seem to have an issue with the 500 record maximum limit since you are importing the table into local storage.  I created a test Excel file  with almost 700 records.  When I imported it into PowerApps I was able to access all the records in the data source. I was also able to filter it on an Id field I included without getting a delegation warning.

 

So within size constraints I would say it can be considered a best practice to load static information like product catalogs into memory by importing an Excel sheet.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

5 REPLIES 5
Dual Super User
Dual Super User

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

Hi @AFWright 

 

This is related to the delegation with respect to excel as a data source

 

You can increase the limit to max 2k

 

Capture.PNG

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

AFWright
Level: Powered On

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

If I wanted to keep this data in an excel file, would the best option be to import the excel file as static data? Would that make a difference?

Dual Super User
Dual Super User

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

@AFWright 

 

Good question - lets check with @Pstork1 

Dual Super User
Dual Super User

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

@AFWright 

 

I haven't done extensive testing, but for a static table of information that doesn't require frequent edits Importing the static Info from Excel can be a good choice. It doesn't seem to have an issue with the 500 record maximum limit since you are importing the table into local storage.  I created a test Excel file  with almost 700 records.  When I imported it into PowerApps I was able to access all the records in the data source. I was also able to filter it on an Id field I included without getting a delegation warning.

 

So within size constraints I would say it can be considered a best practice to load static information like product catalogs into memory by importing an Excel sheet.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
AFWright
Level: Powered On

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

I tested it and it works perfectly and is a lot faster than pinging onedrive using LookUp.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,618)