Forum Discussion

Miss_A's avatar
Miss_A
Regular Visitor
2 years ago
Solved

List.distinct function

= Table.Group(#"Replaced Errors2", {"Sr", "ClinicName", "Month", "age_group", "sex", "dignosis", "Diagnosis_group", "LabCombine.Final Result", "PreTBForm.Screening", "Final Screening Result", "ScreeningResultForDB", "Code"}, {{"UniquedCount", each Table.RowCount(List.Distinct(_[Code])), Int64.Type}})

 

I wirte above function in power query but is only count all the rows are 1. how to cout for the duplicate code

Need Help
  • jennratten's avatar
    jennratten
    2 years ago

    Apologies!  I misunderstood the problem.  Please try this instead.

    Code for the example above:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbIwMTI2MDA2NTRRitWBCxkZGBgYmhgiCwFVGRmYW6IJGZpZmBAWMrAwBwrFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Age Group" = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Age Group", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Age Group", "Code"}, {{"CodeCount", each List.Count ( _[Code] ), type nullable number}})
in
    #"Grouped Rows"

     For your table:

    Table.Group(#"Replaced Errors2", {"Sr", "ClinicName", "Month", "age_group", "sex", "dignosis", "Diagnosis_group", "LabCombine.Final Result", "PreTBForm.Screening", "Final Screening Result", "ScreeningResultForDB", "Code"}, {{"UniqueCount", each List.Count(_[Code]), type nullable number}})

5 Replies

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    Hello - if you are trying to identify duplicate values in the Code column it would be like this:

    Table.Group(#"Replaced Errors2", {"Sr", "ClinicName", "Month", "age_group", "sex", "dignosis", "Diagnosis_group", "LabCombine.Final Result", "PreTBForm.Screening", "Final Screening Result", "ScreeningResultForDB"}, {{"UniquedCount", each List.Count(List.Distinct([Code])), type nullable number}})
    • Miss_A's avatar
      Miss_A
      Regular Visitor
      2 years ago

      It does not work. In code column Code: 01,02,03,04,05,02,03, 05. The fomula show only 1, not 

      84230035141
      84220001411
      84230020791
      84230016841
      84230016841
      84230008741

       

      How is work?

       

  • Miss_A's avatar
    Miss_A
    Regular Visitor
    2 years ago

    It does not work. In code column Code: 01,02,03,04,05,02,03, 05. The fomula show only 1, not 

    84230035141
    84220001411
    84230020791
    84230016841
    84230016841
    84230008741

     

    How is work?

     

    • jennratten's avatar
      jennratten
      Super User
      2 years ago

      Apologies!  I misunderstood the problem.  Please try this instead.

      Code for the example above:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbIwMTI2MDA2NTRRitWBCxkZGBgYmhgiCwFVGRmYW6IJGZpZmBAWMrAwBwrFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Age Group" = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Age Group", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Age Group", "Code"}, {{"CodeCount", each List.Count ( _[Code] ), type nullable number}})
in
    #"Grouped Rows"

       For your table:

      Table.Group(#"Replaced Errors2", {"Sr", "ClinicName", "Month", "age_group", "sex", "dignosis", "Diagnosis_group", "LabCombine.Final Result", "PreTBForm.Screening", "Final Screening Result", "ScreeningResultForDB", "Code"}, {{"UniqueCount", each List.Count(_[Code]), type nullable number}})
  • Miss_A's avatar
    Miss_A
    Regular Visitor
    2 years ago

    Expression.Error: We cannot convert the value 1 to type List.
    Details:
    Value=1
    Type=[Type]