Querying excel imported database for most frequent string in a column

[gotovamsee]

On my powerapp, I have an imported DB from excel table like below. I want to display output of a frequent occuring value from colC depending on a matching condition from colA & colB

ColA	ColB	ColC
X1	Y1	Z1
X1	Y1	Z1
X1	Y1	Z2
X1	Y2	Z3
X1	Y2	Z4
X1	Y2	Z4

Matching condition is entered by the user on the app as an input on the form,

Example

Input matching query: X1 & Y2; Output: Z4

On excel I could use this formula

{=INDEX(C2:C6,MODE((A2:A6="X1")*(B2:B6="Y2")),MATCH(C2:C6,C2:C6,0))}

How can we achieve this in powerapps using functions and conditions so I dont have to hardcode the output in excel?

[Anonymous]

@gotovamsee 

Add a button to your app and add the ClearCollect code to it.

Then add a label and add the CountRows code

[gotovamsee]

@Anonymous 

I get '0' records, doing it multiple ways including your button and label suggestion.

[gotovamsee]

@Anonymous 

Can you suggest how to make this code not case sensitive ?. Today it expects to lookup with case sensitive input only. I tried this lower() expression as below but doesn't work.

LookUp(
    AddColumns(
        Distinct(
            Filter(
                VKGU_Positiondata,
				'Position Title' = Lower(DataCardValue9_1.Text),
                If(!IsBlank(DataCardValue10_1.Text), Country = Lower(DataCardValue10_1.Text), true),
				If(!IsBlank(DataCardValue14_1.Selected.Value), Jobfamily = Lower(DataCardValue14_1.Selected.Value), true),
				If(!IsBlank(DataCardValue15_1.Text), Subfamily = Lower(DataCardValue15_1.Text), true),
				If(!IsBlank(DataCardValue16_1.Selected.Value), Careerstream = Lower(DataCardValue16_1.Selected.Value), true)
				),
            Lower(Job_code)
        ).Result,
        "Count",
        CountRows(
            Filter(
                VKGU_Positiondata,
                Lower(Job_code) = Result
            )
        )
    ),
    Count = Max(Count),
    Result
)

[Anonymous]

@gotovamsee 

Can you confirm that your data source is connected and has data? You could add a new screen, insert a gallery then set it's Items to the entity. This should display ALL your records (in 100 rows of data batches)

[gotovamsee]

I could resolve this case sensitivity issue by applying lower() command only on text fields and excluding them on dropdowns.

[gotovamsee]

@Anonymous 

The app itself is built on a data source, and I have Browse, Details & Edit & Prediction ('Edit' copy) screens with batches of data in it. I don't see any other delegation warnings except on the formula code it appears and looks up only first 500 rows for matching values.

Is there anything I am missing to check to make clearcollect command work?

[Anonymous]

@gotovamsee 

Regarding the 500 records, you can adjust this to get 2000 records. Goto File --> Settings --> Advanced Settings and change the 500 to 2000. This doesn't help though with 22k records. 

Can you share a screen shot of the blank screen with the button you added to try this selected so I can see the code? ClearCollect should simply create an in App collection that you can use, weird.

[gotovamsee]

Thanks @Anonymous  now not only the formula works for 2000 records but also shows the count on clearcollect lablel as 2000.

Please instruct what I can do to make it work for all the records.

[Anonymous]

@gotovamsee 

Now you will need to bring in all your data in 2000 record lots, so 11-12 collections. Then merge them all together. You will first need to work out how to divide up your data. If the ID number you previously mentioned is setup correctly then you should be able to do  this,

ClearCollect(col1, Filter(yourDataSource, ID >= 1 & ID <= 2000);
ClearCollect(col2, Filter(yourDataSource, ID > 2000 & ID <= 4000);
ClearCollect(col3, Filter(yourDataSource, ID > 4000 & ID <= 6000);

// and so on for all collections
// then combine these into one collection using

ClearCollect( colCombined, col1, col2, col3) // add all collection numbers from above that you end up using

Once you've done the above for ALL the collections you need colCombined should have all you records in it.

Note, the above will only work if ID (or whatever the column name is) is a NUMERIC field. If it's a TEXT field you may need to look at a slightly different ClearCollect.

[gotovamsee]

@Anonymous saviour! 

I will now take some time to fiddle with the conversions and post an update in a day. I have few questions to begin with that:

> Can I use any button or gallery from any screen to collect all columns and combine them together or does that have to be specific?

> How do I then use the Colcombined command in the existing code below to bring my matching results? Can I simply replace 'VKGU_Positiondata' with 'Colcombined' ?

LookUp(
    AddColumns(
        Distinct(
            Filter(
                VKGU_Positiondata,
				Lower('Position Title') = Lower(DataCardValue9_1.Text),
                If(!IsBlank(DataCardValue10_1.Text), Lower(Country) = Lower(DataCardValue10_1.Text), true),
				If(!IsBlank(DataCardValue14_1.Selected.Value), Jobfamily = DataCardValue14_1.Selected.Value, true),
				If(!IsBlank(DataCardValue15_1.Text), Lower(Subfamily) = Lower(DataCardValue15_1.Text), true),
				If(!IsBlank(DataCardValue16_1.Selected.Value), Careerstream = DataCardValue16_1.Selected.Value, true)
				),
            Job_code
        ).Result,
        "Count",
        CountRows(
            Filter(
                VKGU_Positiondata,
                Job_code = Result
            )
        )
    ),
    Count = Max(Count),
    Result
)