Forum Discussion
Solved
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 co...
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.
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"
