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

Reuse code inside a data operation

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.

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @AnthonyMartini ,

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.

View solution in original post

5 REPLIES 5
WarrenBelz
Super User III
Super User III

Hi @AnthonyMartini ,

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.

View solution in original post

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.

@AnthonyMartini ,

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.

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?

@AnthonyMartini ,

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,677)