Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MP-iCONN
Resolver I
Resolver I

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

 

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.