Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Keep only unique value (values which does not have duplicates)

I have a big database. I wanted to keep values that appear only one time in on of its column. For this mission, the command "Erase Duplicate" is not adequate. Indeed, if we take the folowing example

 

Imaginary Comlum
1
1
2
3

 

I do not want to have  :

Imaginary Column
1
2
3

 

But :

Imaginary Column
2
3

 

Do you have an idea that would help me?

 

I thank you for the time that you used reading my message,

Yours faithfully,

Cedric

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

    Column1 is the name of column here.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YGRRmDSWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each (List.Count(List.Select(List.Buffer(#"Changed Type"[Column1]),(x)=>x=[Column1])) = 1))
in
    #"Filtered Rows"

     

     👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

    How to get your questions answered quickly -- How to provide sample data

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

    Column1 is the name of column here.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YGRRmDSWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each (List.Count(List.Select(List.Buffer(#"Changed Type"[Column1]),(x)=>x=[Column1])) = 1))
in
    #"Filtered Rows"

     

     👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

    How to get your questions answered quickly -- How to provide sample data