Forum Discussion
Match values across 3 columns
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
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.
2 Replies
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.
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"
