Count null or specific value in a row and display counter in the same row

[YaseenAli]

Hi,

Consider the following table, it is an exam record for students.

There is a name column at the start and then the

Semester Check

column and Semester 1 till Semester 6 columns.

I want to

1. Find the missing represented by null,
2. The failures (any value < .4) and
3. Want to display the failure and missing counter in the Semester Check column

Student Name	Semester Check	Semester 1	Semester 2	Semester 3	Semester 4	Semester 5	Semester 6
Student 1	M-1 F-0	0.44	0.58	0.6	0.54	null	0.88
Student 3	M-2 F-0	null	null	0.64	0.48	0.44	0.52
Student 2	M-0 F-1	0.46	0.56	0.34	0.44	0.68	0.66

 

[jdellasala]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Student Name	Semester Check	Semester 1	Semester 2	Semester 3	Semester 4	Semester 5	Semester 6	Missing	Failures
2	Student 1	M-1 F-0	0.44	0.58	0.6	0.54	null	0.88	1	0
3	Student 3	M-2 F-0	null	null	0.64	0.48	0.44	0.52	2	0
4	Student 2	M-0 F-1	0.46	0.56	0.34	0.44	0.68	0.66	0	1
Sheet2

Cell Formulas
Range	Formula
I2:I4	I2	=COUNTIFS(C2:H2,"=null")
J2:J4	J2	=COUNTIFS(C2:H2,"<"&0.4)

Add the two columns to get the total of both. If you want only one column with the total of both, add the two COUNTIFS statements in one column.

 

[YaseenAli]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Student Name	Semester Check	Semester 1	Semester 2	Semester 3	Semester 4	Semester 5	Semester 6	Missing	Failures
2	Student 1	M-1 F-0	0.44	0.58	0.6	0.54	null	0.88	1	0
3	Student 3	M-2 F-0	null	null	0.64	0.48	0.44	0.52	2	0
4	Student 2	M-0 F-1	0.46	0.56	0.34	0.44	0.68	0.66	0	1
Sheet2

Cell Formulas
Range	Formula
I2:I4	I2	=COUNTIFS(C2:H2,"=null")
J2:J4	J2	=COUNTIFS(C2:H2,"<"&0.4)

Add the two columns to get the total of both. If you want only one column with the total of both, add the two COUNTIFS statements in one column.

Hi,
Thanks for the reply but I'm looking for a PowerQuery solution

 

[jdellasala]

Hi,
Thanks for the reply but I'm looking for a PowerQuery solution

Sorry, but all I got is using a conditional column for each semester and adding the columns together, but that's rediculous. I'm sure there's a few that can come up with something better!

 

[YaseenAli]

Question resolved

I was searching the forum and found this thread similar to my issue, I solved my problem through following code

semMissingCheck = Table.AddColumn(removeOtherCir, "SEE Missing", each ((if [#"Semester 1"] = null then 1 else 0) + (if [#"Semester 2"] = null then 1 else 0) + (if [#"Semester 3"] = null then 1 else 0) + (if [#"Semester 4"] = null then 1 else 0) + (if [#"Semester 5"] = null then 1 else 0) + (if [#"Semester 6"] = null then 1 else 0)) ),

semFailCheck = Table.AddColumn(semMissingCheck, "SEE Fail", each ((if [#"Semester 1"] = null then 0 else if [#"Semester 1"] <.4 then 1 else 0) + (if [#"Semester 2"] = null then 0 else if [#"Semester 2"] <.4 then 1 else 0) + (if [#"Semester 3"] = null then 0 else if [#"Semester 3"] <.4 then 1 else 0) + (if [#"Semester 4"] = null then 0 else if [#"Semester 4"] <.4 then 1 else 0) + (if [#"Semester 5"] = null then 0 else if [#"Semester 5"] <.4 then 1 else 0) + (if [#"Semester 6"] = null then 0 else if [#"Semester 6"] <.4 then 1 else 0))),


addPrefix1 = Table.TransformColumns(semFailCheck, {{"SEE Missing", each "M-" & Text.From(_, "en-US"), type text}}),
addPrefix2 = Table.TransformColumns(addPrefix1, {{"SEE Fail", each "F-" & Text.From(_, "en-US"), type text}}),
mergeSem = Table.CombineColumns(addPrefix2,{"SEE Fail", "SEE Missing"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Sem Check"),

 

[JGordon11]

another way

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    scols = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Semester")),
    tbl = Table.AddColumn(Source, "Semester Check", each 
        let 
            lst = Record.ToList(Record.SelectFields(_, scols)),
            m = List.Count(List.Select(lst, (x)=>x=null)),
            f = List.Count(List.Select(lst, (x)=>x<.4))
        in 
            "M-" & Text.From(m) & " " & "F-" & Text.From(f)
    ),
    Result = Table.ReorderColumns(tbl, {"Student Name", "Semester Check"} & scols)
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Student Name	Semester 1	Semester 2	Semester 3	Semester 4	Semester 5	Semester 6		Student Name	Semester Check	Semester 1	Semester 2	Semester 3	Semester 4	Semester 5	Semester 6
2	Student 1	0.44	0.58	0.6	0.54		0.88		Student 1	M-1 F-0	0.44	0.58	0.6	0.54		0.88
3	Student 3			0.64	0.48	0.44	0.52		Student 3	M-2 F-0			0.64	0.48	0.44	0.52
4	Student 2	0.46	0.56	0.34	0.44	0.68	0.66		Student 2	M-0 F-1	0.46	0.56	0.34	0.44	0.68	0.66
5
Sheet1

 

[YaseenAli]

Thanks, you

another way

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    scols = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Semester")),
    tbl = Table.AddColumn(Source, "Semester Check", each
        let
            lst = Record.ToList(Record.SelectFields(_, scols)),
            m = List.Count(List.Select(lst, (x)=>x=null)),
            f = List.Count(List.Select(lst, (x)=>x<.4))
        in
            "M-" & Text.From(m) & " " & "F-" & Text.From(f)
    ),
    Result = Table.ReorderColumns(tbl, {"Student Name", "Semester Check"} & scols)
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Student Name	Semester 1	Semester 2	Semester 3	Semester 4	Semester 5	Semester 6		Student Name	Semester Check	Semester 1	Semester 2	Semester 3	Semester 4	Semester 5	Semester 6
2	Student 1	0.44	0.58	0.6	0.54		0.88		Student 1	M-1 F-0	0.44	0.58	0.6	0.54		0.88
3	Student 3			0.64	0.48	0.44	0.52		Student 3	M-2 F-0			0.64	0.48	0.44	0.52
4	Student 2	0.46	0.56	0.34	0.44	0.68	0.66		Student 2	M-0 F-1	0.46	0.56	0.34	0.44	0.68	0.66
5
Sheet1

Thanks.
Your solution is much more elegant than the "if then else" code I was doing