Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
= 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
Solved! Go to Solution.
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}})
Expression.Error: We cannot convert the value 1 to type List.
Details:
Value=1
Type=[Type]
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}})
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}})
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?