Remove some records from the table

[stribor45]

I am having some troubles accomplishing following. If i have same id but the countries are different I want to keep country that is not Canada. 

I have table 

code	country
11111	Canada
11111	Albania
22222	Canada
33333	Canada
33333	Belgium
33333	Canada
33333	Belgium
44444	Canada
44444	Canada
44444	Canada

And I need to get here. How would I do this?

code	country
11111	Albania
22222	Canada
33333	Belgium
44444	Canada

[Omid_Motamedise · Accepted solution]

Se this solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQBJR0l58S8xJREpVgdhJBjTlJiXiZEzAgEUJUZgwB2IafUnPTM0lxSlJmAAKoyIoRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t, country = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([country] <> "Canada"))&Source,
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"code"})
in
    #"Removed Duplicates"

[Omid_Motamedise · Accepted solution]

Se this solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQBJR0l58S8xJREpVgdhJBjTlJiXiZEzAgEUJUZgwB2IafUnPTM0lxSlJmAAKoyIoRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t, country = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([country] <> "Canada"))&Source,
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"code"})
in
    #"Removed Duplicates"

[v-aatheeque · Accepted solution]

Hi @stribor45 ,

The answer provided by @Omid_Motamedise  @AlienSx  has resolved your issue? If so, kindly mark the helpful reply as the accepted solution. This will help other community members with similar concerns find solutions more efficiently.
Thank you for your cooperation!"

[stribor45]

can this be adjusted so instead of removing the records just say no if its to be removed and yes if it is to be kept

[AlienSx]

Table.Group(Source, "code", {"country", (x) => List.Skip(x[country], (c) => c = "Canada"){0}? ?? "Canada"})

[stribor45]

Works great. what does this code do

{0}? ?? "Canada"

[AlienSx]

M operators 

?: The item-access-expression also supports the form x{y}?, which returns null when position (or match) y does not exist in list or table x. If there are multiple matches for y, an error is still raised.

?? : Coalesce operator