Looking for a way to show a Custom Column that says Yes if a Value From Row is in Entire Column.

[Anonymous]

Hello. I'm trying to add a custom column that says

if (value in row) is in (entire column) then YES else NO.

Is this possible without using Merge?

[artemus · Accepted solution]

Sure...

if List.Contains(MyTable[SomeColumn], [RowValue]) then
   "YES"
else
   "NO"

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

I have created a sample for your reference, please check the following steps as below.

1. Convert the column to list as below.

2. Then we can add a custom column in the table.

=if List.Contains(Table, [RowValue]) then "Yes" else "No"

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1QGSECoJTKakpCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RowValue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RowValue", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(Table, [RowValue]) then "Yes" else "No")
in
    #"Added Custom"

Attached the pbix as well.

[Anonymous]

THANK YOU!!!

[artemus · Accepted solution]

Sure...

if List.Contains(MyTable[SomeColumn], [RowValue]) then
   "YES"
else
   "NO"

[Anonymous]

Thank you @artemus .  I get errors when trying this. 

Expression.Error: We cannot convert the value "Row Value" to type List.   

I'm working with 100k rows of data.

[artemus]

What are you putting in for MyTable?

It should be the name of the previous step in your query.

[Anonymous]

Thank you @artemus .  I was using the table name.

I replaced with previous step and i'm getting an error

= Table.AddColumn(#"Expanded DATA_REPEAT", "Caused_A_Repeat", each if List.Contains(#"Expanded DATA_REPEAT",[TICKET_NUMBER], [1st_Ticket]) then "YES" else "NO")

Unexpected Error:

There was an error deserializing the evaluation results. The operation might succeed on a retry.

[artemus]

Remove the comma between:

#"Expanded DATA_REPEAT",[TICKET_NUMBER]

[Anonymous]

Thank you again! @artemus 

My file has so many rows of data i think it's unable to perform this lookup. 

[artemus]

Is there a reason why a self merge will not work for you?