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 A Column B Column C New Column 1234 test 23...
Need Help
_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.