Forum Discussion
Comparing lists
Hi everyone! I have a table that has two columns that contain lists of text items and would like to add a third column that would display whether the lists match:
The [List X]{0} differs from [List X]{1} and [List X]{2}, same for [List Y] records.
So I am trying to create a third column that returns for each record "True" if each list member in List X matches the corresponding list member in List Y. I'm not trying to compare the Distinct Members (e.g. using List.Compare). Rather, if [List X]{0} = {1,2,3} and [List Y]{0} = {3,2,1}, then the [New Column]{0} should have "False".
Can someone advise the M code to add this column? I've tried List.Accumulate, but could not figure it out.
Thanks!
Jeff
Hello
as lists can be compared by themselves, meaning {}={} you can use the following syntax
=Table.AddColumn(YourTable, "ListX=ListY", each [List X] = [List Y])
have fun
Jimmy
16 Replies
Hello
as lists can be compared by themselves, meaning {}={} you can use the following syntax
=Table.AddColumn(YourTable, "ListX=ListY", each [List X] = [List Y])
have fun
Jimmy
Wow, worked like a charm! i was definitely overthinking things. I guess I'll put off fully understanding List.Accumulate to another day - no worries!
Thanks.
Unfortunately, i'm getting this error:
"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"
This happens with or without a Table.Addcolumn wrapper. I get it when i run any of the nested functions. When I adapt the code to this:
PositionList = List.Positions(Table.Column(ListInputTable,"List X"){0}),
AddCheckCol = Table.AddColumn(ListInputTable,"Duplicate", each
List.Accumulate(PositionList, true, (s,c) => s and (Table.Column(ListInputTable,"List X"){c} =
Table.Column(ListInputTable,"List Y"){c}))
),I get a table of the desired structure:, but the result is not correct (should be 'TRUE' in Row 2):
Any ideas?
Thanks for the assist,
Jeff
