Forum Discussion

jnixon's avatar
jnixon
Icon for Advocate II rankAdvocate II
6 years ago
Solved

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 fr...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

The code below should handle it.

 

List.Accumulate(List.Positions([List X]), true, (s,c) => s and ([List X]{c} = [List Y]{c}))
jnixon's avatar
jnixon
Icon for Advocate II rankAdvocate II
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    What is the data in the lists?

    • jnixon's avatar
      jnixon
      Icon for Advocate II rankAdvocate II
      6 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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