cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
spirula Frequent Visitor
Frequent Visitor

Slicer for blank and non blank records

I'd like to allow users to select missing records by two slicers: attribute and blank/non-blank.  The attribute slicer is easy but wondering better methods to do the blank, non-blank slicer.  I can't imagine that adding calculated columns to denote each attribute column as containing blank or non-blank records is the best way.  Thoughts?

 

ATTRIBUTES.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
edhans New Contributor
New Contributor

Re: Slicer for blank and non blank records

 @spirula - see the file at the attached link.

 

You cannot just unpivot the table as unpivoting removes all nulls. You will need to replace the nulls with something else. I used zero in my example, but you could use anything that will mesh with the data type. So if it is text, you could use "XXX" or something to filter by to ensure it would never occur in your data.

 

I created a true/false "Is Blank" column in Power Query, then filtered on that. I prefer to do that type of work in PQ if possible because it performs better than calculated columns do.

You can see my original table in the Power Query view by clicking on the gear icon for the "Source" step.

9 REPLIES 9
edhans New Contributor
New Contributor

Re: Slicer for blank and non blank records

Not sure exactly what you are looking for. You mean a record where all atrirbutes are blank?

 

If so, this may not be the best solution but it will work. Add a calculated column:

 

Column = LEN(Table1[Column1] & Table1[Column2] & Table1[Column3] & Table1[Column4])<1

That will return TRUE if the length of all of the concatenated columns are zero. You can then drop that into a slicer. True is blank rows, False is full rows. You could use an IF() function to return something like

 

VAR AggregateLength = LEN(Table1[Column1] & Table1[Column2] & Table1[Column3] & Table1[Column4])
RETURN
    IF(
        AggregateLength = 0,
        "Blank Records",
        "Populated Records"
    )

Then throw that in your slicer.

 

Super User
Super User

Re: Slicer for blank and non blank records

Hi,

 

Using the query Editor, select the first two columns, right click and select "Unpivot other columns".  Now you should be able to use slicers.

spirula Frequent Visitor
Frequent Visitor

Re: Slicer for blank and non blank records

That's what I was thinking too @edhans....given that every attribute column has both blanks and non-blank records, it will require as many columns as there are attirbute columns though.

spirula Frequent Visitor
Frequent Visitor

Re: Slicer for blank and non blank records

Thanks @ashish - I get that all attirbute columns have to be unpivoted to add them to a slicer.  For the blank/non-blank slicer, I'm wondering if there's a better way than having to add a calculated column to read and set blank/non-blank record in each attribute column record.  Thanks.

Super User
Super User

Re: Slicer for blank and non blank records

Hi,

 

I do not understand.  On a sample dataset show your expected result.

spirula Frequent Visitor
Frequent Visitor

Re: Slicer for blank and non blank records

Ashish, sorry that I was not more clear and thanks so much for your time with this.  I need to add two slicers:

1) Attribute Slicer

2) Blank/Non-Blank Slicer

This will allow the user to choose a single or more attributes and see only missing records.

 

--> The attribute slicer is easy enough by unpivoting those 12 attirbute columns and creating that slicer.

--> My question was is having to create 12 calculated columns (one for each attribute) the best way to do this?  Each calculated column would label blank/no-blank (say with an IF Stmt.) One would then unpivot those calculated columns and add them to a second slicer.  Thanks again.

 

Screen Shot 2018-07-28 at 3.05.02 PM.pngTrying to add two slicers.

Super User
Super User

Re: Slicer for blank and non blank records

Hi,

 

Once you have unpivoted the dataset, you will need to create just one calculated column with DAX

 

=IF(ISBLANK(Data[Attribute]),"BLANK","NON BLANK")

 

Hope this helps.

edhans New Contributor
New Contributor

Re: Slicer for blank and non blank records

 @spirula - see the file at the attached link.

 

You cannot just unpivot the table as unpivoting removes all nulls. You will need to replace the nulls with something else. I used zero in my example, but you could use anything that will mesh with the data type. So if it is text, you could use "XXX" or something to filter by to ensure it would never occur in your data.

 

I created a true/false "Is Blank" column in Power Query, then filtered on that. I prefer to do that type of work in PQ if possible because it performs better than calculated columns do.

You can see my original table in the Power Query view by clicking on the gear icon for the "Source" step.

spirula Frequent Visitor
Frequent Visitor

Re: Slicer for blank and non blank records

Thanks @edhans - that did the trick!