cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

SUMPRODUCT Power Query to filter blanks

Afternoon,

 

So I've added a pivot table as a data source which looks like this:

 

Concat180118021803180418051806BLANKS
fn123410022 12 36877FALSE
fn1324510022      TRUE
fe13521687511 6 84FALSE

 

 

Concat = Product Code

1801, 1802 etc = Months of the year

 

In Excel, I would paste the pivot into a new tab and add the BLANKS column to see whether the product had been sold. The formula is: 

 

=SUMPRODUCT(--(B836:AF836<>0))=0

 

Is there an alternative I can use?

 

Secondary question: I'm using a pivot table as source data which would be updated one a month to include new sales data. If I create a Custom Column like the one above, would PBI know to insert a column for the new month or would it override the Custom Column? 

2 REPLIES 2
Highlighted
Super User IV
Super User IV

@bilogin , it should unpivoted

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

 

Also, share the expected output. Not clear with excel formula



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@amitchandak the expected output for the SUMPRODUCT formula is as in the table - if the rows are blank or less than 0 (ie no sales have taken place), the formula will return TRUE. If the value is >0, the cell will return FALSE.

sumproduct.JPG

 

 

 

 

 

 

 

 

 

 

 

 

Pivoting the data didn't work, I tried Sales to Period even though both are numbers, I got the following error:

 

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

 

Thanks for your help.

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors