Forum Discussion

MP-iCONN's avatar
MP-iCONN
Resolver I
4 years ago
Solved

Distinct Count with no blank values using power query

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...
Need Help
  • MP-iCONN's avatar
    MP-iCONN
    4 years ago

    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}})

amitchandak's avatar
amitchandak
Super User
4 years ago

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

MP-iCONN's avatar
MP-iCONN
Resolver I
4 years ago

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}})