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

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
Highlighted
Community Champion
Community Champion

Re: Querying excel imported database for most frequent string in a column

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

Highlighted
Resident Rockstar
Resident Rockstar

Re: Querying excel imported database for most frequent string in a column

@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

Highlighted
Resident Rockstar
Resident Rockstar

Re: Querying excel imported database for most frequent string in a column

@gotovamsee 

Use the exact same structure ie

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

 

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Querying excel imported database for most frequent string in a column

@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
Highlighted
Community Champion
Community Champion

Re: Querying excel imported database for most frequent string in a column

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

Highlighted
Community Champion
Community Champion

Re: Querying excel imported database for most frequent string in a column

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.

Highlighted
Helper II
Helper II

Re: Querying excel imported database for most frequent string in a column

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

Highlighted
Community Champion
Community Champion

Re: Querying excel imported database for most frequent string in a column

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.

Highlighted
Helper II
Helper II

Re: Querying excel imported database for most frequent string in a column

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?

Highlighted
Community Champion
Community Champion

Re: Querying excel imported database for most frequent string in a column

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.

 

Highlighted
Resident Rockstar
Resident Rockstar

Re: Querying excel imported database for most frequent string in a column

@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

Highlighted
Helper II
Helper II

Re: Querying excel imported database for most frequent string in a column

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

Highlighted
Helper II
Helper II

Re: Querying excel imported database for most frequent string in a column

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (9,826)