cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count the missing Number

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.

1 ACCEPTED SOLUTION
Super User
``````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``````
4 REPLIES 4
Frequent Visitor

Hi @AlienSx ,

Frequent Visitor

@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
Super User
``````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``````
Super User
``````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``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors