cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors