Forum Discussion

jj101's avatar
jj101
Frequent Visitor
2 years ago

PowerQuery - Checking if Value in one column appears in a different columan

I am trying to create a column in power query when checking to see if the value in column 'C' resides in column 'B'. Example table below

Column AColumn BColumn CNew Column
1234test2323null
4321kdjkdf12341234
8939dkikdi12341234
383738ueuj89398939

 

This data set currently has over 200,000 rows. I have tried "List.Contains(TableName Column C, Column A)"  in the query editor but does not give the correct in puts. 

Need Help

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    =let a=List.Buffer(TableName[ColumnA]) in Table.AddColumn(TableName,"NewColumn",each if List.Contains(a,[ColumnC] then [ColumnC] else null)

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Hi jj101,

    Please try this code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYoxDsAgDAP/kpmFGKnhLShbqAQZC/9vytTJPp9bo8wolGj1Z0UwGKSpUQHnYLfpdkc5t09IRQ02H27jLyC4ILHsvmfEOaq+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}, {"Column C", Int64.Type}}),
    ListColumnA = Table.Column(Source, "Column A"),
    AddCustomColumn = Table.AddColumn(Source, "New Column", each if List.Contains(ListColumnA, [Column C]) then [Column C] else null)
in
    AddCustomColumn

     

    I'm attaching a pbix file as well.

    • jj101's avatar
      jj101
      Frequent Visitor
      2 years ago

      So when I tried this I receive "PreviewError: The type of the current preview is too complex to display." I have gone in and tried to convert the column to a list and when I do that it basically removes all of the rest of the columns. 

       

      I should note that this source is a database.