cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnixon Regular Visitor
Regular Visitor

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:

  Table.JPG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jimmy801 New Contributor
New Contributor

Re: Comparing 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

View solution in original post

16 REPLIES 16
mcybulski Member
Member

Re: Comparing lists

The code below should handle it.

 

List.Accumulate(List.Positions([List X]), true, (s,c) => s and ([List X]{c} = [List Y]{c}))
jnixon Regular Visitor
Regular Visitor

Re: Comparing lists

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): 

 

Table2.JPG

 

Any ideas?

 

Thanks for the assist,

Jeff

mcybulski Member
Member

Re: Comparing lists

What is the data in the lists?

jnixon Regular Visitor
Regular Visitor

Re: Comparing lists

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:

DataColADataColBDataColC
BenTestBen
DanDanDan
NormNormNorm
ValerieValerieValerie
   

References in data table:

 

Record

List XList Y

1

DataColADataColB
2DataColADataColC
3DataColBDataColC

 

Highlighted
mcybulski Member
Member

Re: Comparing lists

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

 

 

Jimmy801 New Contributor
New Contributor

Re: Comparing 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

View solution in original post

mcybulski Member
Member

Re: Comparing lists

Well that was easy. I did not even think to do that. Thanks for the tip!

jnixon Regular Visitor
Regular Visitor

Re: Comparing lists

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. 

jnixon Regular Visitor
Regular Visitor

Re: Comparing lists

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...

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)