Forum Discussion
Counting Core Courses
Hi syasmin25
If you want to do it in M/ Power query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZA/C4MwEMW/SsnskH8X4+hQ6GAnu4lDCFKk0i76/XsXY4waOLjH4+X9uK5jggtWMJp7U9MmuWF9QYZESfOsX4/V0dHRKFUWgS0CKMls/Th8/YBbaSseTDAGJaSUVBqCoSQlzF4E+OJ/4oKQw+UIG9sKFxh+fnTTrZ0Xf4XcOTLAI8YJsEqJxAfGxq7GTS4vsVDGq36Wd4r0fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Period = _t, Subject = _t, School_ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Period", Int64.Type}, {"Subject", type text}, {"School_ID", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Subject] = "ELA" or [Subject] = "MATH")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"School_ID", "ID"}, {{"NumSubjectsInAbsence", each Table.RowCount(_), type number}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"School_ID"}, {{"NumStudents", each List.Count(List.Select([NumSubjectsInAbsence],each _=2))}}) in #"Grouped Rows1"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I know you posted this in Power Query, but it is more easily solved in DAX. Load your example table, and then use a measure expression like this to get the count of students that were absent from both ELA and Math. Doing it in DAX lets you keep all your other data for different analyses.
Absent ELA and Math =
COUNTROWS (
FILTER (
VALUES ( Absences[ID] ),
CALCULATE ( COUNTROWS ( Absences ), Absences[Subject] IN { "ELA", "Math" } ) = 2
)
)
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi syasmin25
If you want to do it in M/ Power query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZA/C4MwEMW/SsnskH8X4+hQ6GAnu4lDCFKk0i76/XsXY4waOLjH4+X9uK5jggtWMJp7U9MmuWF9QYZESfOsX4/V0dHRKFUWgS0CKMls/Th8/YBbaSseTDAGJaSUVBqCoSQlzF4E+OJ/4oKQw+UIG9sKFxh+fnTTrZ0Xf4XcOTLAI8YJsEqJxAfGxq7GTS4vsVDGq36Wd4r0fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Period = _t, Subject = _t, School_ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Period", Int64.Type}, {"Subject", type text}, {"School_ID", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Subject] = "ELA" or [Subject] = "MATH")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"School_ID", "ID"}, {{"NumSubjectsInAbsence", each Table.RowCount(_), type number}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"School_ID"}, {{"NumStudents", each List.Count(List.Select([NumSubjectsInAbsence],each _=2))}}) in #"Grouped Rows1"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers