Loop through galleries until find data > 0

[rohitkushwaha]

I'm new to powerapps, I want to loop through galleries until data is greater than 0.

Currently, I'm using another gallery (Gallery2) to make the Gallery1 ThisItem.Columnname dynamic using switch functions.

Data1:

I need solutions for the following: 
If "ThisItem.3", the second row has no data or 0, then it should go to "thisitem.2", if there is no data then it should go and check "Thisitem.1", now "Thisitem.1" has data, not the loop should stop.

[rohitkushwaha]

Hahaha! I thought we can figure out the number of days later first let’s focus on how to get out of the current problem(calculating the blanks until you find data). My bad!

You mean you’ll give me the column numbers which have blanks until we find data? 

If yes, then I can figure out the differences between start and end dates as days using week number table. But single row will have multiple blank cells so we have to pick each number and find in week number table then find the differences and keep adding the days.

[Chris-D]

Morning! 

I've finished modifying the code so it can be put straight into a Gallery.Items property. 

Just want to clear something up, in your examples, you're using column 3 as the point of calculation but there are 5 columns. Because of this, I made an input parameter specifying the column number to work with. Do you need this input parameter or will it always be the same? Like, always 2 before the total number of columns, etc? 

[rohitkushwaha]

I need input parameter because the column will change based on today’s date.

For example December month:

Week number 1: 1/12/2023 to 1/12/2023

Week number 2: 4/12/2023 to 8/12/2023 (Based on today’s date Swtich statement will pick week number 2 because 4/12/2023 falls under week number 2 range)

Week number 3: 11/12/2023 to 15/12/2023

Week number 4: 18/12/2023 to 22/12/2023

so on…

Week number will not always be the same like 3 or  like 2 before the total number of columns.

[Chris-D]

@rohitkushwaha, just to show I'm making progress haha! 

This can now be put straight into your gallery. It adds a column to the original data called 'Unallocated' which shows the number of unallocated 'weeks' for each employee. 

It's grown quite a bit more complicated so I've added the input source at the top. 

// Input parameters
With({
    EmployeeData: EmployeeWeekData,
    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 failsafe 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),
                                        { NotBlank: 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 employee data to store number of blank weeks
        AddColumns(
            EmployeeData,
            // Add column to calculate number blank weeks
            "Unallocated", ThisWeekNum -LookUp(EmployeeAllocationLookUpTable, ID = EmployeeData[@ID]).NotBlank
        )
    )
)

Next thing you want is to look up the number of days that those 'weeks' amount to, right? 


EDIT:

I did this?

[rohitkushwaha]

@Chris-D  Fabulous!

Let me put the code in gallery (items) and check. I’ll update you on this.

And Yes!, Days column result is correct in the screenshot. Difference between the start date and end date.

[Chris-D · Accepted solution]

@rohitkushwaha, I tidied up the code that I used to produce the above, here it is! 

I quit my job so might not be around much longer. I'm happy to still help you but if I don't reply you know why 😉

You'll probably need to change the filter near the end for the week number look up table, your table probably has a different column name is all. 

// 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
            )
        )
    )
)

[rohitkushwaha]

Congratulations 🎉 @Chris-D 

I would like to stay connected with such brilliant mind if it’s ok please drop your LinkedIn on any social media.

I’m trying the code, I have one question - what you put on the gallery > label > text to show the columns? 

Also if possible could you please attach the source files for the reference? 

[Chris-D]

@rohitkushwaha, the extra columns are:

Unallocated: The number of blanks preceeding and including the current 'week'

TotalDays: The number of days that span the unallocated weeks.

I've attached the source as example. Sample data is defined in OnVisible.

You can see examples of my work on

https://chris.dabrowa.uk 

I'll PM you with my contact details, would be happy to help out. 

[rohitkushwaha]

I tried your code and it worked!! 🎉

Thank you so much @Chris-D 

[Chris-D]

@rohitkushwaha happy to help, this was an interesting problem 🙂