Forum Discussion
Conditional Column with variables
I want to compare the text strings in four columns. If the string is the same in all four columns, then the score is 6. If none of the strings match, then 0. I can create six conditional columns ...
HI Anonymous
Whichever way you approach this, I think there will be a a few steps to the calculation.
However, to write it in a general way, I would do something like this:
= Table.AddColumn( Source, "Score", each let ValuesList = {[A],[B],[C],[D]}, // Could rewrite if you need it to be dynamic ValuesColumn = Table.FromList(ValuesList, each {_}), GroupedCount = Table.Group(ValuesColumn,{"Column1"},{"Count", Table.RowCount, Int64.Type})[Count], PairsCount = List.Sum(List.Transform(GroupedCount, each Number.Combinations(_,2))) in PairsCount, Int64.Type )There could be a smarter or less verbose approach - will give it some more thought 🙂
Regards
OwenAuger
Super User
Super UserHI Anonymous
Whichever way you approach this, I think there will be a a few steps to the calculation.
However, to write it in a general way, I would do something like this:
= Table.AddColumn(
Source,
"Score",
each let
ValuesList = {[A],[B],[C],[D]}, // Could rewrite if you need it to be dynamic
ValuesColumn = Table.FromList(ValuesList, each {_}),
GroupedCount =
Table.Group(ValuesColumn,{"Column1"},{"Count", Table.RowCount, Int64.Type})[Count],
PairsCount =
List.Sum(List.Transform(GroupedCount, each Number.Combinations(_,2)))
in
PairsCount,
Int64.Type
)
There could be a smarter or less verbose approach - will give it some more thought 🙂
Regards
Thank you OwenAuger for the elegant solution!