Count the missing Number

[MightyRabbit]

Hi everyone, 

I need help with query and hope you guys can help. I have a table like this:

Number	Code	IO
123	A	1
123	B
456	A	1
456	B
789	A	1
911	A	1

If number has code A & the IO = 1, so they should have the same number with Code B in the table.
My question is how could I figure out, the total number of missing code B, when the have IO = 1. 
In this case, the result should be 2 (789 and 911).

Thank you very much. 

[MightyRabbit]

Hi @AlienSx , 

appreciate your help. 

[MightyRabbit]

@AlienSx ; Thank you so much 🙂
Just one more thing, how could I Filter out these number in a table, that means I want to have a table with Code A, IO = 1, but missing B. 

789	A	1
911	A	1

[AlienSx]

let
    Source = your_table,
    code_io = Table.AddColumn(Source, "code_io", each Text.Combine({[Code], Text.From([IO])}, ""), type text),
    gr = Table.Group(code_io, "Number", {{"all", each _}}),
    select = Table.SelectRows(gr, each List.Contains([all][code_io], "A1") and not List.Contains([all][Code], "B")),
    expand = Table.ExpandTableColumn(select, "all", {"Code", "IO"})
in
    expand

[AlienSx · Accepted solution]

Hi, @MightyRabbit 

let
    Source = your_table,
    code_io = Table.AddColumn(Source, "code_io", each Text.Combine({[Code], Text.From([IO])}, ""), type text),
    gr = Table.Group(code_io, "Number", {{"all", each _}}),
    select = Table.SelectRows(gr, each List.Contains([all][code_io], "A1") and not List.Contains([all][Code], "B")),
    out = Table.RowCount(select)
in
    out