cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Querying excel imported database for most frequent string in a column

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

 
 

 

ColAColBColC
X1Y1Z1
X1Y1Z1
X1Y1Z2
X1Y2Z3
X1Y2Z4
X1Y2Z4

 

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?

4 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Hi @gotovamsee ,

You did not say how you were going to present the values of ColA and ColB so that ColC result could be displayed, however assuming they are entered into two Text Inputs called ColATxt and COlBTxt, the formula to show the ColC matching result would be

Lookup(
   YourDataSourceName,
   ColA = ColATxt.Text &&
   ColB = ColbTxt.Text,
   ColC
)

OR

Lookup(
   YourDataSourceName,
   ColA = ColATxt.Text &&
   ColB = ColbTxt.Text
).ColC

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

@WarrenBelz cheers for the tag.

 

@gotovamsee so long as you don't have any delegation issues this might work on the DataCard relevant to "ColC"

LookUp(
    AddColumns(
        Distinct(
            Filter(
                Positiondata,
                'Position Title' = DataCardValue9.Text,
                'Country' = DataCardValue10.Text
            ),
            Newjobcode
        ).Result,
        "Count",
        CountRows(
            Filter(
                Positiondata,
                Nwejobcode = Result
            )
        )
    ),
    Count = Max(Count),
    Result
)

Note, I've assumed 

 - Position Title = ColA,

 - Country = ColB, and

 - Newjobcode = ColC

 

Let me know how you get on.

 

Note: There will be a delegation warning with the above code but you can ignore this if your data set is <2000 records

 

 

View solution in original post

@gotovamsee 

Use the exact same structure ie

If(!IsBlank(DataCardValue10_1), 'Country' = DataCardValue10_1.Text, true)

 

View solution in original post

@gotovamsee ,

Also if your list is not sorted in strict numeric order (which it should be with ID), this will work on any list that has a number in every instance of a numeric field.

ClearCollect(
    colMyRecords,
    Sort(
        VKGU_Positiondata,
        Sequence
    )
);
If(
    CountRows(colMyRecords) = 2000,
    Set(
        vID,
        Max(
            colMyRecords,
            Sequence
        )
    );
    Collect(
        colMyRecords,
        Sort(
            Filter(
                VKGU_Positiondata,
                Sequence > vID
            ),
            Sequence
        )
    )
);
If(
    CountRows(colMyRecords) = 4000,
    Set(
        vID,
        Max(
            colMyRecords,
            Sequence
        )
    );
    Collect(
        colMyRecords,
        Sort(
            Filter(
                VKGU_Positiondata,
                Sequence > vID
            ),
            Sequence
        )
    )
)

View solution in original post

51 REPLIES 51
Super User III
Super User III

Hi @gotovamsee ,

You did not say how you were going to present the values of ColA and ColB so that ColC result could be displayed, however assuming they are entered into two Text Inputs called ColATxt and COlBTxt, the formula to show the ColC matching result would be

Lookup(
   YourDataSourceName,
   ColA = ColATxt.Text &&
   ColB = ColbTxt.Text,
   ColC
)

OR

Lookup(
   YourDataSourceName,
   ColA = ColATxt.Text &&
   ColB = ColbTxt.Text
).ColC

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Hi @gotovamsee ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

@WarrenBelz Sorry I could not test your solution due to something unexpected

 

Very close but only works with any one expression of the two. Adding both using 'Or' operation throws an invalid argument error expecting Boolean, Number, Text, OptionSetValue

 

Here is how my formula looks like now (removed an extra closing brace at the end).

 

LookUp( Positiondata, 'Position Title' =
DataCardValue9.Text && 'Country' = DataCardValue10.Text, Newjobcode )

Or

LookUp( Positiondata, 'Position Title' =
DataCardValue9.Text && 'Country' = DataCardValue10.Text). Newjobcode

 

What is the second part of the formula doing?

Hi @gotovamsee ,

Sorry - they were two formula options, not one formula. I will remember in future to put them in two separate boxes.

I have found different regional syntaxes prefer one over the other. I have also fixed the typo on the second formula.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Perfect @WarrenBelz ,

 

So to understand, it is picking the first matching combination instead of most frequntly occuring.

 

How to make it work for the most frequent matching output?

Hi @gotovamsee ,

That is an interesting one. I can do collection and a gallery showing the numbers if you want, but getting the most frequent by itself is not something I have managed to get close to after about 20 minutes of testing.

I will tag my code guru colleague @Eelman to see if he has any ideas.

 

@WarrenBelz cheers for the tag.

 

@gotovamsee so long as you don't have any delegation issues this might work on the DataCard relevant to "ColC"

LookUp(
    AddColumns(
        Distinct(
            Filter(
                Positiondata,
                'Position Title' = DataCardValue9.Text,
                'Country' = DataCardValue10.Text
            ),
            Newjobcode
        ).Result,
        "Count",
        CountRows(
            Filter(
                Positiondata,
                Nwejobcode = Result
            )
        )
    ),
    Count = Max(Count),
    Result
)

Note, I've assumed 

 - Position Title = ColA,

 - Country = ColB, and

 - Newjobcode = ColC

 

Let me know how you get on.

 

Note: There will be a delegation warning with the above code but you can ignore this if your data set is <2000 records

 

 

View solution in original post

I fully appreciate your time spent on this. Thank you @WarrenBelz 

Many thanks @Eelman . Let me quickly check this solution and get back. By the I have 23k records. Can delegation be an issue?

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (47,698)