Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
1 year ago
Solved

Remove some records from the table

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 

 

codecountry
11111Canada
11111Albania
22222Canada
33333Canada
33333Belgium
33333Canada
33333Belgium
44444Canada
44444Canada
44444Canada

 

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

 

codecountry
11111Albania
22222Canada
33333Belgium
44444Canada
Need Help
Show and Tell
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    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's avatar
    v-aatheeque
    1 year ago

    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!"

6 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    1 year ago
    Table.Group(Source, "code", {"country", (x) => List.Skip(x[country], (c) => c = "Canada"){0}? ?? "Canada"})
    • stribor45's avatar
      stribor45
      Post Prodigy
      1 year ago

      Works great. what does this code do

       

      {0}? ?? "Canada"
      • AlienSx's avatar
        AlienSx
        Super User
        1 year ago

        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 

  • stribor45's avatar
    stribor45
    Post Prodigy
    1 year ago

    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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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's avatar
      v-aatheeque
      Community Support
      1 year ago

      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!"