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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Trying to add two slicers.Trying to add two slicers.

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks @edhans - that did the trick!

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
edhans
Super User
Super User

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

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.