Counting Core Courses

[syasmin25]

I have an issue and am having trouble wrapping my head around it. I am trying to count students absent in English and Math classes. The issue here is the database places students absent per period on a row to row basis. I have created a sample here. The original one is over a million rows.

I was wondering if there is any way I can create a column that counts number of students absent in MATH and ELA. I know I can do table. But I need to be looking more like the picture below (red font is where I want the calculation) and was wondering if someone could help.

[mahoneypat]

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

[AlB · Accepted solution]

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 

[artemus]

1. Filter out non-Math/Eng rows

2. Do a group on school id, having Count rows as the aggregate.

This should give you the count you are looking for.