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
Advocate IIUnfortunately, 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 Let me know if this is what you are looking for. I am sure that my creation of the cross lists is too cumbersome, but it shows the list.accumulate in action.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrNU9JRCkktLgFSIE6sTrSSSyJIEEGCxPzyi3KBXGQKJByWmJNalJkKFMJkxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DataColA = _t, DataColB = _t, DataColC = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DataColA", type text}, {"DataColB", type text}, {"DataColC", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {}, {{"A", each List.Combine({[DataColA]}), type list}, {"B", each List.Combine({[DataColB]}), type list},{"C", each List.Combine({[DataColC]}), type list}}), RemoveC = Table.RemoveColumns(#"Grouped Rows",{"C"}), ListAB = Table.RenameColumns(RemoveC,{{"A", "List X"}, {"B", "List Y"}}), RemoveB = Table.RemoveColumns(#"Grouped Rows",{"B"}), ListAC = Table.RenameColumns(RemoveB,{{"A", "List X"}, {"C", "List Y"}}), RemoveA = Table.RemoveColumns(#"Grouped Rows",{"A"}), ListBC = Table.RenameColumns(RemoveA,{{"B", "List X"}, {"C", "List Y"}}), Combine = ListBC & ListAC & ListAB, AddCheckColumn = Table.AddColumn(Combine, "Custom", each List.Accumulate(List.Positions([List X]), true, (s,c) => s and ([List X]{c} = [List Y]{c}))) in AddCheckColumn- jnixon
For the record, this indeed worked, using my "tblInput" as the Source. And I'm going to have to understand what you did with that initial JSON function! My guess is that you scraped the post and used some sort of converter to generate the binary? Or perhaps it was a pointer to the post itself? In any case, thanks for the help...