cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gotovamsee
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
WarrenBelz
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
WarrenBelz
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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,119)