Gallery filter - powerapps

Question

I have table with 60 columns for e.g the table looks like below

Name  Process1         Process2 ..............  Process59
A            1             2                         0
B            4             0                         0
C            2             3                         1

In the powerapp - i have already got the name from the user. in our case if the user picks "A" then i want my gallery to only show the process columns where A has value greate than 0

in this case if "A" is selected then the gallery will only display Process 1, process 2 and its value 

if the user selects "B" then only Process 1 will get displayed 

How do i go about achieving it - i know i have to play with filter - but how do i loop through all columns in the table to find value greater than 0 for the selected user?

Answer 1

You cannot have an expression that directly filters and gives the column names as results... What you can do is unpivot your table, so that you have a structure like the one below:

Name  Process    Value
A     Process1   1
A     Process2   2
...
A     Process59  0
B     Process1   4
B     Process2   0
...
B     Process59  0
C     Process1   2
C     Process2   3
...
C     Process59  1

Once you have your data in that format, you can apply the filter based on the column values.

To create the unpivoted table, you can use an expression like this one (assuuming that your data source is called 'MyDataSource'):

    Clear(UnpivotedCollection);
    ForAll(
            MyDataSource,
            Collect(
                    UnpivotedCollection,
                    { Name: Name, Attribute: "Process1", Value: Process1 },
                    { Name: Name, Attribute: "Process2", Value: Process2 },
                    { Name: Name, Attribute: "Process3", Value: Process3 },
                    ...
                    { Name: Name, Attribute: "Process58", Value: Process58 },
                    { Name: Name, Attribute: "Process59", Value: Process59 }))

If you have this expression in the App's OnStart property (or in some other place where you can refresh the unpivoted collection after updates to the data source), you can start using it. Now, if you have a Dropdown control with the following expression for the Items property:

MyDataSource.Name

You can then have a gallery with the following expression for its Items property:

Filter(
    UnpivotedCollection,
    Name = Dropdown1.Selected.Name
    Value > 0)

And you will have in the gallery the records for the given name with positive values.