Distinct Count with no blank values using power query

[MP-iCONN]

I am grouping columns in Power Bi via Power Query and I am trying to get a distinct count of my Work Orders.  The one issue I am having is I do not want to count the NULL/Blank values.

Here is my Power Query statement:

= Table.Group(#"Removed Columns1", {"Item ID", "Item Name", "Bin Qty", "On Hand Qty", "EOQ", "Order Point Qty", "CUS_CorpName", "3BIN", "5BIN", "8BIN", "Bins - Stock", "Customer Name", "Backlog", "WKO Status Code", "3BIN%", "5BIN%", "8BIN%", "Blanket Qty"}, {{"SUM WKO QTY To Complete", each List.Sum([WKO QTY To Complete]), type number}, {"WOs", each Table.RowCount(Table.Distinct(_)), Int64.Type}})

So for the nulls it is showing as 1 after grouping when it should show 0.

Thank you for any help you can give.

Chad

[amitchandak]

@MP-iCONN , is it possible to filter the null values before group by ?

[MP-iCONN · Accepted solution]

In this report I still need to show the nulls.

I was able to solve this with this statement:

= Table.Group(#"Removed Columns1", {"Item ID", "Item Name", "Bin Qty", "On Hand Qty", "EOQ", "Order Point Qty", "CUS_CorpName", "3BIN", "5BIN", "8BIN", "Bins - Stock", "Customer Name", "Backlog", "WKO Status Code", "3BIN%", "5BIN%", "8BIN%", "Blanket Qty"}, {{"SUM WKO QTY To Complete", each List.Sum([WKO QTY To Complete]), type number}, {"WOs", each Table.RowCount(Table.SelectRows(_,(x)=>x[WKO Work Order ID]<>null)), Int64.Type}})