Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
Sure...
if List.Contains(MyTable[SomeColumn], [RowValue]) then
"YES"
else
"NO"
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.
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.
THANK YOU!!!
Sure...
if List.Contains(MyTable[SomeColumn], [RowValue]) then
"YES"
else
"NO"
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.
What are you putting in for MyTable?
It should be the name of the previous step in your query.
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. |
Remove the comma between:
#"Expanded DATA_REPEAT",[TICKET_NUMBER]
Thank you again! @artemus
My file has so many rows of data i think it's unable to perform this lookup.
Is there a reason why a self merge will not work for you?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.