Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

remove duplicates based on certain criteria

Hi Everyone, 

 

I wondered if in power query it's possible to remove duplicate values based on certain criteria. My data would look like this:

 

 

IDClientStageFeeCountryurlSource
2ABC.win500FRabc.compipeline
3XXX  FRxxx.comintake
1MMM     
2ABCwin500 FR intake
2abc 100 abc.comintake
3XXXlost100NL pipeline

 

I want to remove the duplicate values based on ID but source = "pipeline". Is it possible to achieve this?

 

Any small help would be appreciated.

 

Thanks in advance.

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can add a step using Table.SelectRows function, please change #"Changed Type" to the name of your previous step.

     

     

    RemoveDuplicate = Table.SelectRows(#"Changed Type",each let s=[Source] ,i = [ID] ,  totalSameIDNumber = Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]=i)) in totalSameIDNumber=1 or s="pipeline")

     

     

     

    All the queries are here:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUXJ0ctYDUuWZeUDS1MAASLoFAYnEpGS95PxcIKsgsyA1JzMvVSlWJ1rJGCgQEREBJBWgGKy6oqICqjozryQxG6LWEMj19fVFUovAIHmo9ci2ww1UQDXKCOIiqIQh2JkKKK5EUoxwY05+cQlcvZ8PVBPCQ7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Client = _t, Stage = _t, Fee = _t, Country = _t, url = _t, Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Client", type text}, {"Stage", type text}, {"Fee", Int64.Type}, {"Country", type text}, {"url", type text}, {"Source", type text}}),
    RemoveDuplicate = Table.SelectRows(#"Changed Type",each let s=[Source] ,i = [ID] ,  totalSameIDNumber = Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]=i)) in totalSameIDNumber=1 or s="pipeline")
in
    RemoveDuplicate

     


    If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


    Best regards,

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can add a step using Table.SelectRows function, please change #"Changed Type" to the name of your previous step.

     

     

    RemoveDuplicate = Table.SelectRows(#"Changed Type",each let s=[Source] ,i = [ID] ,  totalSameIDNumber = Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]=i)) in totalSameIDNumber=1 or s="pipeline")

     

     

     

    All the queries are here:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUXJ0ctYDUuWZeUDS1MAASLoFAYnEpGS95PxcIKsgsyA1JzMvVSlWJ1rJGCgQEREBJBWgGKy6oqICqjozryQxG6LWEMj19fVFUovAIHmo9ci2ww1UQDXKCOIiqIQh2JkKKK5EUoxwY05+cQlcvZ8PVBPCQ7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Client = _t, Stage = _t, Fee = _t, Country = _t, url = _t, Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Client", type text}, {"Stage", type text}, {"Fee", Int64.Type}, {"Country", type text}, {"url", type text}, {"Source", type text}}),
    RemoveDuplicate = Table.SelectRows(#"Changed Type",each let s=[Source] ,i = [ID] ,  totalSameIDNumber = Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]=i)) in totalSameIDNumber=1 or s="pipeline")
in
    RemoveDuplicate

     


    If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


    Best regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Wow... this works really well!!! thank you so much v-lid-msft !!.

      I kinda found out the solution my own way by grouping the table then merges it, but yours works way much cleaner!!! 

       

      Thanks a lot!!