Filling in Missing Data Between Two Dates with 0 in a Collection

[Caz_16]

Hello, 

I have a Sharepoint List that records a data point (A percentage value) for the Key Fields of Person, Project, and Month. 

From this data, I do various calculations and display the data to my user in various ways, but there is one scenario Im very much struggling with. 

When a Person is assigned to a Project, they are NOT necesarrily assigned to the project every month, resulting in gaps in the data, a quick sample:

Person	Project	Month	Percentage
Bob	Project X	09/01/2023	50%
Bob	Project X	12/01/2023	69%

So when I try to graph this data, I get a graphs in PowerApps that look like so:

So you can see that on the X axis, the dates jump a couple of months. What I am trying to do is show 0% for the months in-between. Unfortunately, I cannot writeback to the dataset "0"s for the months that dont have data. I can do this in PowerBI, but unfortunately I cannot crack it in PowerApps. 

So my ideal solution (from the example above) would look like the following:

Person	Project	Month	Percentage
Bob	Project X	09/01/2023	50%
Bob	Project X	10/01/2023	0%
Bob	Project X	11/01/2023	0%
Bob	Project X	12/01/2023	69%

So far, Ive been able to use the code below to create the following table (Note- _EAPKey is a context var defined elsewhere in the app and Allocation is the name of my SP List):

UpdateContext(
    {
        _MinDate: First(
            SortByColumns(
                ShowColumns(
                    Filter(
                        Allocation,
                        SharePointID_Allocation_ProjectList = _EAPKey,
                        Month2 <= Date(
                            Year(Today()) + 2,
                            Month(Today()),
                            Day(Today())
                        )
                    ),
                    "Month2"
                ),
                "Month2",
                SortOrder.Ascending
            )
        ),
        _MaxDate: First(
            SortByColumns(
                ShowColumns(
                    Filter(
                        Allocation,
                        SharePointID_Allocation_ProjectList = _EAPKey,
                        Month2 <= Date(
                            Year(Today()) + 2,
                            Month(Today()),
                            Day(Today())
                        )
                    ),
                    "Month2"
                ),
                "Month2",
                SortOrder.Descending
            )
        )
    }
);
ClearCollect(
    _ProjectionCollection,
    Ungroup(
        ForAll(
            Distinct(
                RenameColumns(
                    ShowColumns(
                        Filter(
                            Allocation,
                            Project_Key = _EAPKey,
                            Month2 <= Date(
                                Year(Today()) + 2,
                                Month(Today()),
                                Day(Today())
                            )
                        ),
                        "Percent_x0020_Allocation",
                        "Title",
                        "Month2",
                        "ID"
                    ),
                    "Title",
                    "Employee_Key"
                ),
                Employee_Key
            ) As Temp2,
            ForAll(
                Sequence(
                    DateDiff(
                        _MinDate.Month2,
                        _MaxDate.Month2,
                        TimeUnit.Months
                    ),
                    0
                ),
                {
                    Employee_Key: Temp2.Value,
                    Month2: DateAdd(
                        _MinDate.Month2,
                        ThisRecord.Value,
                        TimeUnit.Months
                    ),
                    Project_Key: _EAPKey
                }
            )
        ),
        "Value"
    )
)

Person	Project	Month
Bob	Project X	09/01/2023
Bob	Project X	10/01/2023
Bob	Project X	11/01/2023
Bob	Project X	12/01/2023

However, where I am struggling is now using Collect or ForAll to get the %'s from the SharePoint List (called Allocation) for the months that have data, and fill in 0's where there isn't a data point. 

Any help would be appreciated!

[poweractivate]

You can further modify the collection by iterating through the dates and checking if a record exists for that particular month in the SharePoint list. If a record does exist, use the percentage value, otherwise, use 0%.

Here's a formula example that could help you achieve this:

ClearCollect(
    colFinalResult,
    ForAll(
        _ProjectionCollection,
        LookUp(
            Allocation,
            SharePointID_Allocation_ProjectList = _EAPKey && Month2 = ThisRecord.Month2
        ),
        If(
            IsBlank(ThisRecord),
            {
                Employee_Key: _ProjectionCollection[@Employee_Key],
                Month2: _ProjectionCollection[@Month2],
                Project_Key: _ProjectionCollection[@Project_Key],
                Percentage: 0
            },
            {
                Employee_Key: _ProjectionCollection[@Employee_Key],
                Month2: _ProjectionCollection[@Month2],
                Project_Key: _ProjectionCollection[@Project_Key],
                Percentage: ThisRecord['Percent_x0020_Allocation']
            }
        )
    )
)

This formula first looks up the Allocation list based on the project key and month, then checks if a record is found. If no record is found, it fills in the 0% value; otherwise, it fills in the actual percentage value from the Allocation list.

The final result is stored in the colFinalResult collection, which you can use in your charts or other visualizations.

See if it helps @Caz_16 

[poweractivate · Accepted solution]

@Caz_16 

In my formula notice I repeat here 

               Employee_Key: _ProjectionCollection[@Employee_Key],
                Month2: _ProjectionCollection[@Month2],
                Project_Key: _ProjectionCollection[@Project_Key],

Here's a formula example below where I try to improve on this and remove the repetition:

ClearCollect(
    colFinalResult,
    ForAll(
        _ProjectionCollection,
        With(
            {
                wCommonKeys: {
                    Employee_Key: _ProjectionCollection[@Employee_Key],
                    Month2: _ProjectionCollection[@Month2],
                    Project_Key: _ProjectionCollection[@Project_Key]
                },
                wAllocation: LookUp(
                    Allocation,
                    SharePointID_Allocation_ProjectList = _EAPKey && Month2 = _ProjectionCollection[@Month2]
                )
            },
            If(
                IsBlank(wAllocation),
                Patch(wCommonKeys, { Percentage: 0 }),
                Patch(wCommonKeys, { Percentage: wAllocation['Percent_x0020_Allocation'] })
            )
        )
    )
)

See if it helps as well @Caz_16 

[Caz_16]

This worked like a charm. Thank you so much. Ill admit I'm not 100% sure how this is working, guess I need to do some homework on the formulas. Also good job on picking up on that SharepointID_Allocation_ProjectsList was the same as the Project Key, I missed that one in my code when I was simplifying it and removing my stupidly long column names. 

Thanks again for the help!

-Caz

[poweractivate]

@Caz_16 

The formula is designed to fill in the missing data points with 0% in a SharePoint List dataset for your scenario.

Here's an explanation of the formula in case it may help you:

1. ClearCollect: This function initializes or clears a collection named colFinalResult. This collection will be populated with the final dataset including the missing percentage values as 0.

2. ForAll: This function goes through the existing collection _ProjectionCollection, which contains the months and keys (like Person and Project) you need to work with.

3. With: Within the ForAll, you are using a With function to create two scoped variables: wCommonKeys and wAllocation.

   • wCommonKeys: This consists of the common keys you need for each record (Employee Key, Month, and Project Key), so you don't have to repeat them.

   • wAllocation: This scoped variable performs a LookUp on the Allocation SharePoint List, searching for records that match both the project key (_EAPKey) and the month (_ProjectionCollection[@Month2]).

4. If: Then you are using an If function to check whether the wAllocation variable is blank, meaning there's no existing data for the particular month in the SharePoint List.

   • If it is blank (i.e., no record found for the given month), you are patching the wCommonKeys with a Percentage value of 0.

   • If it is not blank (i.e., a record is found), you are patching the wCommonKeys with the actual percentage value from the wAllocation.

The result from the Patch(wCommonKeys,{ Percentage: ...) itself is actually a merged single Record containing the common keys and the appropriate percentage, like this:

{
   Employee_Key: _ProjectionCollection[@Employee_Key],
   Month2: _ProjectionCollection[@Month2],
   Project_Key: _ProjectionCollection[@Project_Key]
   Percentage: //appropriate value goes here due to the conditional Patch
}

5. Result: The result of this process is the collection colFinalResult, which includes the original data and fills in 0% for the missing data points.

The formula leverages scoping through the With function to simplify access to common variables, and it efficiently fills in the missing data points by iterating through the necessary range of dates.

The difference between the formula I initially gave and this one I gave more recently is that I additionally removed the need to repeat the same keys of a record in both sides of an If, by using With, and using a record containing common keys, and at the end there using Patch(record,record) to merge the common keys record with the difference and get a resulting record that has the common keys and has the difference we want.

See if this explanation above helps @Caz_16 

[Caz_16]

I am getting a delegation error on this part of the formula on the Month2 = _ProjectionCollection[@Month2]

wAllocation: LookUp(
                    Allocation,
                    SharePointID_Allocation_ProjectList = _EAPKey && Month2 = _ProjectionCollection[@Month2]
                )
            },

I'm not sure why either, because Month2 is a date column in SP, and according to the documentation, a lookup and "=" are both delegable. Is it because its trying to iterate through a table? I wish these errors gave more information about WHY the function isn't delegable. Because as far as I can see, it should be. 

[poweractivate]

@Caz_16 
Here's what I would try if I were to try something really quickly:

Try my version I gave first without the "With" optimization and if it works, go with that one.

ClearCollect(
    colFinalResult,
    ForAll(
        _ProjectionCollection,
        LookUp(
            Allocation,
            SharePointID_Allocation_ProjectList = _EAPKey && Month2 = ThisRecord.Month2
        ),
        If(
            IsBlank(ThisRecord),
            {
                Employee_Key: _ProjectionCollection[@Employee_Key],
                Month2: _ProjectionCollection[@Month2],
                Project_Key: _ProjectionCollection[@Project_Key],
                Percentage: 0
            },
            {
                Employee_Key: _ProjectionCollection[@Employee_Key],
                Month2: _ProjectionCollection[@Month2],
                Project_Key: _ProjectionCollection[@Project_Key],
                Percentage: ThisRecord['Percent_x0020_Allocation']
            }
        )
    )
)

The optimized version is more ideal, but it's not that optimal to be worth the delegation issue.
The only real loss of optimization here is more on the management side. If you change the repeated keys you have to change them twice instead of once. However if this removes the delegation issue, it's more than worth it to just have the repetition.

The actual performance side may actually be around equal to, or slightly hindered by the "optimized" With scope version because there may be extra steps for the With scope for the LookUp and the common keys, and the With scope is not being used enough times to increase the performance and outweigh the overhead of establishing the scope to begin with. Besides this, the Patching of common keys and difference may be actually slightly more inefficient than the inline specification and repeating twice. 

The "optimization" of my With version was much more intended to be on the formula management side, as the base records are themselves repeated twice in the unoptimized version. If we go back and change it later, we have to change it twice, instead of only once in the "optimized" version. 

However this type of management optimization is outweighed by delegation. If the initial version I gave, the repeating version, has no delegation issue it's ultimately much more optimal in all ways, than the one that gives the delegation issue, if indeed the With scopes are the reason it's giving the delegation issue.

If for some reason, it still gives the delegation warning, continue working on the above unoptimized version I gave first to be sure, and check the LookUp

SharePointID_Allocation_ProjectList = _EAPKey && Month2 = ThisRecord.Month2

Try it without the Month2 = side, and then try it without the _EAPKey side, see which still gives the warning (or do both still give the warning even in isolation?)

See if it helps @Caz_16 

[Caz_16]

So I have been fiddling with this for most of the day today and just can't seem to get anything to work. A couple of things that Ive found:
First, unfortunately in your most recent reply, ThisRecord isn't an available variable at 2 different points in the code (below you can see it's underlined red and not a suggestion). The [@ProjectKey] I just got around by using _EAPKey instead.

I assume you are trying to access the row that you are currently returning from inside of the LookUp, which I also tried to do a number of ways, unfortunately each way I went about it, something else was going wrong. Either my SP List returned no data, or it would return errors. The closest I got to was with this code below, but it was still throwing a delegation error. 

ClearCollect(
    colFinalResult,
    ForAll(
        _ProjectionCollection,
       
    {
                Employee_Key: _ProjectionCollection[@Employee_Key],
                Month2: _ProjectionCollection[@Month2],
                Project_Key:  _EAPKey,
                Percentage: LookUp(
                    Allocation,
                    SharePointID_Allocation_ProjectList = _EAPKey && Title = Employee_Key && Month2 = Month2
                ).Allocation_Allocation_SP
            }
)
)

 Also tried, with the same result:

ClearCollect(
    colFinalResult,
    ForAll(
        _ProjectionCollection,
        With({_Allocation:
            LookUp(
                Allocation,
                SharePointID_Allocation_ProjectList = _EAPKey && Title = _ProjectionCollection[@Employee_Key] && Month2 = _ProjectionCollection[@Month2]
            ).Allocation_Allocation_SP
        },
        {
            Employee_Key: _ProjectionCollection[@Employee_Key],
            Month2: _ProjectionCollection[@Month2],
            Project_Key: _EAPKey,
            Percentage: _Allocation
        }
    )
))

 As far as the delegation error on your optimized version, it is something about the Month2 = _ProjectionCollection[@Month2] where it is unhappy. The other parts of the Filter are fine and do not throw delegation errors. What's strange is when I replace _ProjectionCollection[@Month2] with something static like Date(2023,8,1), the delegation error goes away.

So right now I am using the version with the code from the original accepted solution. I also upped my delegation limit from 500 to 750 (I dont want to go all the way to 2,000 as that would definitely cause some of my users computers to crash if they were to try and store that much data in memory).

Thanks, Ill post anything else that I can think of to try. 

Caz