Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 for each of the pair combinations, and then add up the score.
I was hoping to do this in one step.
Something like this.
= Table.AddColumn(source, Score, each
vAB = if [A] = [B] then 1 else 0,
vAC = if [A] = [C] then 1 else 0,
vAD = if [A] = [D] then 1 else 0,
vBC = if [B] = [C] then 1 else 0,
vBD = if [B] = [D] then 1 else 0,
vCD = if [C] = [D] then 1 else 0,
vScore = vAB + vAC + vAD + vBC + vBD + vCD
)
I prefer to do this in Powerquery instead of DAX.
I am probably missing a simple syntax.
Thank you for any suggestions.
Solved! Go to Solution.
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
Hi @MarkusEng1998, different approach here.
You have to define column names to calc here:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxOggRIzCGiRjAMUjECMpDqDHAqsYYiE2B2ARujjFUF1AkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
ColumnNamesToCalc = List.Buffer({"A", "B", "C", "D"}),
ColumnCombinations = List.Buffer(List.Accumulate(
List.Zip({ {0..List.Count(ColumnNamesToCalc)-1}, ColumnNamesToCalc }),
{},
(s,c)=> s & [ x = List.RemoveMatchingItems(List.Skip(ColumnNamesToCalc, c{0}+1), {c{1}}),
y = List.Repeat({c{1}}, List.Count(x)),
z = List.Zip({ y, x })
][z]
)),
ChangedType = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
Ad_Result = Table.AddColumn(ChangedType, "Result", each
List.Sum(List.Transform(ColumnCombinations, (x)=>
[ a = Record.FieldOrDefault(_, x{0}, null),
b = Record.FieldOrDefault(_, x{1}, null),
c = if a = b then 1 else 0
][c] )), type number)
in
Ad_Result
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