Forum Discussion
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", "Screen...
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}})
It does not work. In code column Code: 01,02,03,04,05,02,03, 05. The fomula show only 1, not
| 8423003514 | 1 |
| 8422000141 | 1 |
| 8423002079 | 1 |
| 8423001684 | 1 |
| 8423001684 | 1 |
| 8423000874 | 1 |
How is work?
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}})