Solved: Re: Loop through galleries until find data > 0

rohitkushwaha · ‎11-24-2023

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.

Chris-D · ‎12-07-2023

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

View solution in original post

Chris-D · ‎11-24-2023

Hi @rohitkushwaha, you can use the Coalesce() function to do this. It returns the first non blank value from a list.

I'm assuming you want to start at column 5 and find the first column towards 1 that has a value, this should do that:

Coalesce( ThisItem.5, ThisItem.4, ThisItem.3, ThisItem.2, ThisItem.1)

rohitkushwaha · ‎11-24-2023

@Chris-D
Thanks for the reply.

Let me elaborate on how I need the output.
If "ThisItem.3" is blank or 0 in the data (second row) I need 2 as a value in the gallery table because ThisItem.3 and Thisitem.2 are blank or 0 but Thisitem.1 has a value > 0, see the expected output below.

Expected Output:

Chris-D · ‎11-24-2023

Is this specifically only in the second row that you need this calculation? Why is the answer 2? I don't understand that. Because there are 2 blank values? Are we counting the number of blanks?

rohitkushwaha · ‎11-24-2023

No, it's not specific to the second row, it's for all rows. The answer is 2 because Thisitem.3 is blank and Thisitem.2 is blank but Thisitem.1 has value, even though If ThisItem.0 had value we don't have to consider that because we found the data in ThisItem.1 so we have to stop here.

I've added extra data in the table to explain you in depth.

See below table,
Data1:

If Thisitem.3 is selected then the gallery should show the below output.
Example:
Row 2 - This.item3 and Thisitem2 are blank but Thisitem.1 has value so the output will be 2.
Row 6 - It has data so the output will be 0.
Row 9 - Thisitem.3, Thisitem.2 and Thisitem.1 are blank so the output will be 3 (We just have to check before data, if Thisitem.3 then Thisitem.2 and Thisitem.1, if Thisitem.4 is blank then Thisitem.3, Thisitem.2 and Thisitem.1 and so on)
Row 10 - Thisitem.3 is blank so output will be 1. (We don't have to go ahead and check if before Thisitem has value like in this Thisitem.2 has value so we don't have to go and check Thisitem.1)

Expected output:

Chris-D · ‎11-24-2023

ok, so the formula is the number of blank rows up to column 3?

This should do that, but it won't check the order of columns for blanks or not.

CountRows( Filter( [ThisItem.1, ThisItem.2, ThisItem.3], IsBlank(Value)))

e.g.
1, Blank, Blank = 2

Blank, 1, Blank = 2

I'm not sure if that's a problem for you?

rohitkushwaha · ‎11-24-2023

For example we are taking column 3 it can be 4,5 so on.

Output should be:

If we are considering column 3

eg:

blank, blank, 1 = 0

1, blank, blank = 2

blank,1, Blank = 1 (Because 2nd column has value, we don’t have to look for blanks ahead)

Chris-D · ‎11-24-2023

I think the easiest thing here is to just do an If() since there are only 3 possibilities.

If( IsBlank(ThisItem.3), 
    If( IsBlank(ThisItem.2),
        If( IsBlank( ThisItem.1), 
            3, // 3, 2 and 1
            2  // 3 and 2
        ),
        1 // 3 only
    ),
    0 // none
)

rohitkushwaha · ‎11-24-2023

What if there are 100s of Thisitem?

How can I handle this?

Chris-D · ‎11-24-2023

You can't use column names dynamically so unfortunately you couldn't have a loop of 100 to say column 1, column 2, etc.

I could probably do it using your source data, but you'd have to show me that. You said you were putting into a 2nd gallery, that's where ThisItem is coming from I'm guessing, so where is it before it's in the gallery?

Loop through galleries until find data > 0

Helpful resources

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

Updates to Transitions in the Power Platform Communities