I already have below code in my gallery, how can i merge your code into it? please help
// Input parameters
With({
EmployeeData: EmployeeWeekData,
WeekNumberLookUpTable: WeekNumbers,
ThisWeekNum: 3
},
With({
// Create list to store employee non allocated weeks
EmployeeAllocationLookUpTable:
// For each employee, find the last non blank column up to specified week column
ForAll( EmployeeData As EWD2,
// Find the last non blank column
Last( Filter(
// Create a table of employees with their last non-blank week column values
// This list will have multiple entries for each employee, one for each non blank column
// UnGroup to resolve nested data structure
Ungroup(
// For each employee
ForAll( EmployeeData As EWD1, {
ID: EWD1.ID,
Name: EWD1.Name,
Data: Filter(
// Search all weeks, recording any column which isn't blank
ForAll( Sequence( ThisWeekNum +1, 0) As ColNum,
With({
// Switch the column to get the value
ColValue: Switch(
ColNum.Value,
// Need this to ensure there is at least one non-blank value
0, "Not Blank Failsafe",
1, EWD1.One,
2, EWD1.Two,
3, EWD1.Three,
4, EWD1.Four,
5, EWD1.Five
)
},
// If column not blank, return data
If( !IsBlank( ColValue),
{ NotBlankCol: ColNum.Value}
)
)
),
// Remove blanks - columns where value is blank will return blank record
!IsBlank( ThisRecord)
)
}),
"Data" // Merge nested data
),
ID = EWD2.ID
))
)
},
// Add a column to count total unallocated days
AddColumns(
// Add a column to employee data to store number of blank weeks
AddColumns(
EmployeeData,
// Add column to calculate number blank weeks
"Unallocated", ThisWeekNum -LookUp(EmployeeAllocationLookUpTable, ID = EmployeeData[@ID]).NotBlankCol
),
// Create new column total days
"TotalDays",
// Use Coalesce to insert 0 instead of blank
Coalesce(
// Total days is the sum of....
Sum(
// Add number of days per week to week lookup table
AddColumns(
// Use only unallocated weeks up to this week
Filter( WeekNumberLookUpTable, Week > ThisWeekNum -Unallocated && Week <= ThisWeekNum),
"Days", DateDiff( StartDate, EndDate) +1
),
// Sum the days column
Days
),
// Sum could be blank if no unallocated days
// Put a 0 instead
0
)
)
)
)
