Forum Discussion
Compare values for multiple columns which may change dynamically
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!
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"
2 Replies
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"It worked!
Thank you very much ThxAlot. I wish it was that easy for me 😁!