Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Power Query / performance issue
Power Apps - Power Query
Unanswered

performance issue

(0) ShareShare
ReportReport
Posted on by RV-16061027-0 14
If(CountRows(Filter(colRowMonthData,UnitType = 0)) = 0 ,
ForAll(
    colRowMonthData As Alpha,
    Patch(
        Objectives,
        LookUp(
            Objectives,
            'Objective ID' = Alpha.'Objective ID'
        ),
        {
            'Result jan': If(
                Alpha.Unit <> Unit.text,
               Value(Alpha.ResultJan.Text),
                Blank()
            ),
            'Result jan t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultJan.Text,
                Blank()
            ),
            'YTD jan': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDJan.Text),
                Blank()
            ),
            'YTD jan t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDJan.Text,
                Blank()
            ),
            'Comment (itobj_jan_comment)': Alpha.ComJan.Text,
            Frequency: Alpha.Frequency,
 
            'Result feb': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.FebResult.Text),
                Blank()
            ),
            'Result feb t': If(
                Alpha.Unit = Unit.text,
               Alpha.FebResult.Text,
                Blank()
            ),
            'YTD feb': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.FebYTD.Text),
                Blank()
            ),
            'YTD feb t': If(
                Alpha.Unit = Unit.text,
                Alpha.FebYTD.Text,
                Blank()
            ),
            'Comment (itobj_feb_comment)': Alpha.FebComment.Text,
 
            'Result mar': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultMar.Text),
                Blank()
            ),
            'Result mar t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultMar.Text,
                Blank()
            ),
            'YTD mar': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDMar.Text),
                Blank()
            ),
            'YTD mar t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDMar.Text,
                Blank()
            ),
            'Comment (itobj_mar_comment)': Alpha.ComMar.Text,
 
            'Result apr': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultApr.Text),
                Blank()
            ),
            'Result apr t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultApr.Text,
                Blank()
            ),
            'YTD apr': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDApr.Text),
                Blank()
            ),
            'YTD apr t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDApr.Text,
                Blank()
            ),
            'Comment (itobj_apr_comment)': Alpha.ComApr.Text,
           
            'Result may': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultMay.Text),
                Blank()
            ),
            'Result may t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultMay.Text,
                Blank()
            ),
            'YTD may': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDMay.Text),
                Blank()
            ),
            'YTD may t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDMay.Text,
                Blank()
            ),
            'Comment (itobj_may_comment)': Alpha.ComMay.Text,
 
            'Result jun': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultJun.Text),
                Blank()
            ),
            'Result jun t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultJun.Text,
                Blank()
            ),
            'YTD jun': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDJun.Text),
                Blank()
            ),
            'YTD jun t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDJun.Text,
                Blank()
            ),
            'Comment (itobj_jun_comment)': Alpha.ComJun.Text,
 
            'Result jul': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultJul.Text),
                Blank()
            ),
            'Result jul t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultJul.Text,
                Blank()
            ),
            'YTD jul': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDJul.Text),
                Blank()
            ),
            'YTD jul t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDJul.Text,
                Blank()
            ),
            'Comment (itobj_jul_comment)': Alpha.ComJul.Text,
 
            'Result aug': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultAug.Text),
                Blank()
            ),
            'Result aug t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultAug.Text,
                Blank()
            ),
            'YTD aug': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDAug.Text),
                Blank()
            ),
            'YTD aug t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultAug.Text,
                Blank()
            ),
            'Comment (itobj_aug_comment)': Alpha.ComAug.Text,
 
            'Result sep': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultSep.Text),
                Blank()
            ),
            'Result sep t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultSep.Text,
                Blank()
            ),
            'YTD sep': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDSep.Text),
                Blank()
            ),
            'YTD sep t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDSep.Text,
                Blank()
            ),
            'Comment (itobj_sep_comment)': Alpha.ComSep.Text,
 
            'Result oct': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultOct.Text),
                Blank()
            ),
            'Result oct t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultOct.Text,
                Blank()
            ),
            'YTD oct': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDOct.Text),
                Blank()
            ),
            'YTD oct t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDOct.Text,
                Blank()
            ),
            'Comment (itobj_oct_comment)': Alpha.ComOct.Text,
 
            'Result nov': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultNov.Text),
                Blank()
            ),
            'Result nov t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultNov.Text,
                Blank()
            ),
            'YTD nov': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDNov.Text),
                Blank()
            ),
            'YTD nov t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDNov.Text,
                Blank()
            ),
            'Comment (itobj_nov_comment)': Alpha.ComNov.Text,
 
            'Result dec': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.ResultDec.Text),
                Blank()
            ),
            'Result dec t': If(
                Alpha.Unit = Unit.text,
                Alpha.ResultDec.Text,
                Blank()
            ),
            'YTD dec': If(
                Alpha.Unit <> Unit.text,
                Value(Alpha.YTDDec.Text),
                Blank()
            ),
            'YTD dec t': If(
                Alpha.Unit = Unit.text,
                Alpha.YTDDec.Text,
                Blank()
            ),
            'Comment (itobj_dec_comment)': Alpha.ComDec.Text,
           
            'YTD Colour Jan': Coalesce(Alpha.colorValueJanYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Jan'
            ),
            'YTD Colour Feb': Coalesce(Alpha.colorValueFebYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Feb'
            ),
            'YTD Colour Mar': Coalesce(Alpha.colorValueMarYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Mar'
            ),
            'YTD Colour Apr': Coalesce(Alpha.colorValueAprYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Apr'
            ),
            'YTD Colour May': Coalesce(Alpha.colorValueMayYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour May'
            ),
            'YTD Colour Jun': Coalesce(Alpha.colorValueJunYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Jun'
            ),
            'YTD Colour Jul': Coalesce(Alpha.colorValueJulYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Jul'
            ),
            'YTD Colour Aug': Coalesce(Alpha.colorValueAugYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Aug'
            ),
            'YTD Colour Sep': Coalesce(Alpha.colorValueSepYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Sep'
            ),
            'YTD Colour Oct':Coalesce(Alpha.colorValueOctYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Oct'
            ),
            'YTD Colour Nov': Coalesce(Alpha.colorValueNovYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Nov'
            ),
            'YTD Colour Dec': Coalesce(Alpha.colorValueDecYTD,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'YTD Colour Dec'
            ),
            'Result Colour Jan': Coalesce(Alpha.colorValueJanResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Jan'
            ),
            'Result Colour Feb': Coalesce(Alpha.colorValueFebResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Feb'
            ),
            'Result Colour Mar': Coalesce(Alpha.colorValueMarResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Mar'
            ),
            'Result Colour Apr': Coalesce(Alpha.colorValueAprResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Apr'
            ),
            'Result Colour May': Coalesce(Alpha.colorValueMayResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour May'
            ),
            'Result Colour Jun': Coalesce(Alpha.colorValueJunResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Jun'
            ),
            'Result Colour Jul': Coalesce(Alpha.colorValueJulResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Jul'
            ),
            'Result Colour Aug': Coalesce(Alpha.colorValueAugResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Aug'
            ),
            'Result Colour Sep': Coalesce(Alpha.colorValueSepResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Sep'
            ),
            'Result Colour Oct': Coalesce(Alpha.colorValueOctResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Oct'
            ),
            'Result Colour Nov': Coalesce(Alpha.colorValueNovResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Nov'
            ),
            'Result Colour Dec': Coalesce(Alpha.colorValueDecResult,
            LookUp(colMyTempObjective, 'Objective ID' = Alpha.'Objective ID').'Result Colour Dec'
            )
        }
    )
);
Clear(colRowMonthData);
 
ClearCollect(
    colObjectivesData,
    Filter(
        Objectives,
        'Objectives (Views)'.'My Objectives'
    )
),
UpdateContext({varwarningPopup:true}));
Set(
    glbVarLoadingSpinner,
    false
);
using this code for saving data to Dataverse. But it is taking so much of time.
Categories:
General Comment
  • WarrenBelz Profile Picture
    WarrenBelz 148,859 Most Valuable Professional on at
    performance issue
    The error will not be the structure (I assume you are using SharePoint) - I use this on almost every gallery or collection patch to a data source. It will be somewhere in your table structure.
  • RV-16061027-0 Profile Picture
    RV-16061027-0 14 on at
    performance issue
    the code is not working. If give Forall inside the patch.
  • WarrenBelz Profile Picture
    WarrenBelz 148,859 Most Valuable Professional on at
    performance issue
    A quick follow-up to see if you received the answer you were looking for or if you need further assistance.

    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • WarrenBelz Profile Picture
    WarrenBelz 148,859 Most Valuable Professional on at
    performance issue
    Firstly what @stampcoin has posted is the "big winner" here. 
    ForAll is not designed to be a loop, although it can act that way if it contains an action inside it. ForAll creates a Table, which can be Patched in one action to the data source and will run much faster than individual Patches for each record. If it contains the ID of each record, it will update the specific records (as below), if not it will create new records.
     
    There are also a couple of other things that have significant multiple duplications which may benefit from pre-defining.
    If(
   CountRows(
      Filter(
         colRowMonthData,
         UnitType = 0
      )
   ) = 0,
   Patch(
      Objectives,
      ForAll(
         colRowMonthData As Alpha,
         With(
            {
               _Record:
               LookUp(
                  Objectives,
                  'Objective ID' = Alpha.'Objective ID'
               ),
               _Alpha: Alpha.Unit = Unit.text
            },            
            {
               ID: _Record.ID,
               'Result jan': If(
                  !_Alpha,
                  Value(Alpha.ResultJan.Text),
                  Blank()
               ),
               'Result jan t': If(
                  _Alpha,
                  Alpha.ResultJan.Text,
                  Blank()
               ),
               'YTD jan': If(
                  !_Alpha,
                  Value(Alpha.YTDJan.Text),
                  Blank()
               ),
               'YTD jan t': If(
                  _Alpha,
                  Alpha.YTDJan.Text,
                  Blank()
               ),
               'Comment (itobj_jan_comment)': Alpha.ComJan.Text,
               Frequency: Alpha.Frequency,
               . . . . . . . . . ,
               
               'YTD Colour Jan': Coalesce(
                  Alpha.colorValueJanYTD,
                  _Record.'YTD Colour Jan'
               ),
               'YTD Colour Feb': Coalesce(
                  Alpha.colorValueFebYTD,
                  _Record.'YTD Colour Feb'
               ),
               . . . . . . . . . ,
               
               'Result Colour Jan': Coalesce(
                  Alpha.colorValueJanResult,
                  _Record.'Result Colour Jan'
               ),
               'Result Colour Feb': Coalesce(
                  Alpha.colorValueFebResult,
                  _Record.'Result Colour Feb'
               )
            )
         }
      )
   )
);
. . . . . .
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • stampcoin Profile Picture
    stampcoin 3,314 Super User 2025 Season 2 on at
    performance issue
    Row-by-row Patch inside ForAll, will cause the performance problem ( big one) ,every record in colRowMonthData triggers its own network call to the Objectives data source.
     
    Try below one if it can improve the performance first(it can be optimized further). There are another ways as well
    If(
    CountRows(Filter(colRowMonthData, UnitType = 0)) = 0,

    // Batch patch instead of individual patches
    Patch(
        Objectives,
        ForAll(
            colRowMonthData As Alpha,
            {
                'Objective ID': Alpha.'Objective ID',

                'Result jan': If(Alpha.Unit <> Unit.Text, Value(Alpha.ResultJan.Text), Blank()),
                 //others, the same as you have right now.
                Frequency: Alpha.Frequency
            }
        )
    );
    //the rest
）
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Now Available: 2025 Release Wave 2
Welcome to the Power Platform Communities
Announcing our 2025 Season 2 Super Users!

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Last month Overall leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans