Compare values for multiple columns which may change dynamically

[Anonymous]

@OwenAugerhelped me solve this problem for comparing values between four columns (A, B, C & D) with a scoring system on the pairs that matched. Each of these lettered columns represents a project name.  I am struggling with dynamically creating ValuesList = {[A], [B], [C], ... }

= Table.AddColumn(
  Source,
  "Score",
  each let
    ValuesList = {[A],[B],[C],[D]}, 
    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
)

I was able to dynamically rename my desired columns to the ABC sequence with: = Table.RenameColumns(Source,RenameColList, MissingField.Ignore)

// RenameColList
let
    Source = "Projects",
    #"20removeDupl" = Table.Distinct(Source, {"projName"}),
    #"21addNdx" = Table.AddIndexColumn(#"20removeDupl", "ndx", 1, 1, Int64.Type),
    #"30addAlfaIndex" = Table.AddColumn(#"21addNdx", "AlfaIndex", each Character.FromNumber ([ndx]+64), type text),
    #"40removeCols" = Table.SelectColumns(#"30addAlfaIndex",{"projName", "AlfaIndex"}),
    #"50addColList" = Table.AddColumn(#"40removeCols", "renameCols", each {[projName], [AlfaIndex]}),
    renameCols = #"50addColList"[renameCols]
in
    renameCols

I tried changing the original code to this, but the score all returns zero, instead of values 0-6 (count of pair matches).

= Table.AddColumn(
  Source,
  "Score",
  each let
    ProjColumns = Table.RemoveColumns(Source,{"rmRevised", "dRofusField", "Index"}),
    ValuesList = List.Buffer(Table.ColumnNames(ProjColumns)), 
...

Please advise.

Thank you!

[Anonymous]

It worked!

Thank you very much @ThxAlot. I wish it was that easy for me 😁!

[ThxAlot · Accepted solution]

Easy enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiU6VYHYgoMjYBi5pA1RpBRYFqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Added Column" = Table.AddColumn(Source, "Score", each List.Sum(let l = Record.ToList(_), len = List.Count(l) in List.TransformMany({0..len-1}, each List.Skip(l, _+1), (x,y) => if l{x} = y then 1 else 0)))
in
    #"Added Column"