Forum Discussion
Advocate IIComparing lists
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
The code below should handle it.
List.Accumulate(List.Positions([List X]), true, (s,c) => s and ([List X]{c} = [List Y]{c}))
jnixonUnfortunately, 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
What is the data in the lists?
- jnixon
The table is a cross join of three lists: DataColA, B and C below. List "DataColA" = List "DataColC" <> List "DataColB". Essentially, this is supposed to check to see if any column in the input table is a duplicate of any other column in that table.
So there should be a True for Row 2 record and a False for the others:
Lists:
DataColA DataColB DataColC Ben Test Ben Dan Dan Dan Norm Norm Norm Valerie Valerie Valerie References in data table:
Record
List X List Y 1
DataColA DataColB 2 DataColA DataColC 3 DataColB DataColC