Replace all values that are not of a certain set in one column

[clecle]

Hi,

I am trying to replace all values that are not in the following list by -2.

I am not sure how to go about it.

When the value is NOT in the following list, I want it to be -2:

{2166,3033,5175,0883}

Here is what I see when I click on advanced  editor:

let
Source = OData.Feed("some link", null, [Implementation="2.0"]),
Issues_table = Source{[Name="Issues",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(Issues_table, each true),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ISSUE_ID", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,-1,Replacer.ReplaceValue,{"ISSUE_ID"}),
#"Sorted Rows1" = Table.Sort(#"Replaced Value",{{"ISSUE_ID", Order.Ascending}})
in
#"Sorted Rows1"

Thanks!

[m_dekorte · Accepted solution]

Hi @clecle ,

I noticed a number starting with a 0 please know that the value for the number 0883 is 883

Anyway, give this a go:

Table.ReplaceValue(Source,each [ISSUE_ID],each if List.Contains({2166,0883,3033,5175}, [ISSUE_ID]) then [ISSUE_ID] else -2,Replacer.ReplaceValue,{"ISSUE_ID"})

Ps. If this helps solve your query please mark this post as Solution, thanks!