Match values across 3 columns

[Trebor84]

Hi,

Hopefully this isn’t too confusing.

I have a simple table in Power Query with 4 columns of numbers A-D

I need to create a conditional column in Power Query to check if the value in column A is greater than the maximum value of whatever is in columns B-D, if A is greater then the value of A goes into the new column if not then there is an additional else if formula below.

If there is a match in columns B-D between any two of the values, for example if the values in B-D are 86 21 86 then that would be true as 86 is listed twice, 86 would then go in the new column.

As a final else the check needs to see if any single value in B-D is greater than what’s in A then the new column needs to display “Check” if that’s true.

all 4 columns could be zero so in that case the new column would also display zero.

Thanks

[AlexisOlson · Accepted solution]

You can write a custom column for this with a formula something like this:

if [A] > List.Max({[B], [C], [D]}) then [A]
else if List.Count(List.Distinct({[B], [C], [D]})) < 3 then List.Mode({[B], [C], [D]})
else "Check"

Note that the zeros case is a special case of the match in B-D so doesn't need separate logic.

Be aware that this outputs a text in some cases and numbers in others and mixing data types in a single column is not usually a great idea.

[Trebor84]

Thanks, this worked great.

Please see my final code below, I added an additional text column to display the results.

if [A] >= List.Max({[B], [C], [D]}) then [A]
else if List.Count(List.Distinct({[B], [C], [D]})) < 3 then List.Mode({[B], [C], [D]})
else List.Max({[B], [C], [D]})

if [A] >= List.Max({[B], [C], [D]}) then "Column A has max value"
else if List.Count(List.Distinct({[B], [C], [D]})) < 3 then "At least two other columns are greater than A"
else "Check"