Answered

Reuse code inside a data operation

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have this piece of code to pull data from a SharePoint and put it into a collection called 'SpendData'. The collection is made by taking a list of retailers and adding two columns, 'SearchAmount' and 'iMediaAmount' and giving these columns values based on another list, 'Data'. The reason why I don't create the collection based on 'Data' alone is because new retailers may not be included in the list.

ClearCollect(SpendData,ShowColumns(AddColumns(RetailerList,
 "SearchAmount",
 0 + If(Approved,LookUp(Data,Retailer=RetailerList[@Title]&&Title=CategoryV).CurrentAmount,LookUp(Data,Retailer=RetailerList[@Title]&&Title=CategoryV).PendingAmount),
 "iMediaAmount",
 0 + If(Approved,LookUp(Data,Retailer=RetailerList[@Title]&&Title=CategoryV).iMedia_x002f_OtherCurrent ,LookUp(Data,Retailer=RetailerList[@Title]&&Title=CategoryV).iMedia_x002f_OtherPending)),"Title","SearchAmount","iMediaAmount")

I have two main issues with this code:
-In the 'If' statements, I repeat the lookup for each condition. Is there any way that I can do the lookup and have the if statement simply decide which attribute to pull.

- The code runs slow, sometimes taking 13 seconds to load which is a little annoying. Is there anything I can do to speed this up?

Is there anything I can do to make this more concise and/or faster? Thank you for your help.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

I cannot test this (and free-typed so watch commas, brackets etc), but this structure may speed it up - I assume Data is under 2,000 items (as the lookup would not work anyway if it was)

With( { wData: RenameColumns( Data, "Title", "TitleD" ) }, ClearCollect( SpendData, ShowColumns( AddColumns( RetailerList, With( { wDataVal: LookUp( wData, Retailer=Title && TitleD=CategoryV ) }, "SearchAmount", If( Approved, wDataVal.CurrentAmount, wDataVal.PendingAmount ), "iMediaAmount", If( Approved, wDataVal.iMedia_x002f_OtherCurrent, wDataVal.iMedia_x002f_OtherPending ) ) ), "Title", "SearchAmount", "iMediaAmount" ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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

Like
a
(0)

Report
Copy link

Link copied!

It's not letting me nest the 'With' function how you have it. It throws errors saying that the addColumns function only has two arguments, when it requires three.

Was this reply helpful? Yes No
WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,

OK - you can still repeat all of that (the main purpose was to avoid that), but the main thing was the With() at the top which causes the Lookup at the bottom to be done "locally" rather than back to the data source.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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

Like
a
(0)

Report
Copy link

Link copied!

But wouldn't that have to load the whole data source, taking longer? The data I'm pulling does not make up the entire data source. Maybe this would be solved with a filter?

Was this reply helpful? Yes No
WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,

How big is the data source? I believe the issue is the Lookup going back to the data each time (your question was about performance). A smaller data source does not take long to pull back once and then the lookup is done locally - although you still have one data call (your original code involves going back to two data sources on each lookup).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No