Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 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.

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you OwenAuger for the elegant solution!

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Anonymous, 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