![[UPDATED] Trick to show items with no data in PivotTables - including Power Pivot! - My Online Training Hub](https://i3.ytimg.com/vi/qODYbzgZwus/maxresdefault.jpg)
[UPDATED] Trick to show items with no data in PivotTables - including Power Pivot! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Henk-Jan
Hi Mynda,
I actually ran into this problem today at work (Office 2016) using power pivot based data. After having created the dim-table and replacing the relevant field from the original table with the similar field from the dim-table everything worked like a charm....however, my report based on several getpivotdata-functions (through VBA) throwed an object-error...due to the field names in the getpivotdata-functions still referring to the old table field, i.e. these had to be updated as well to refer to the field name of the new dim-table, updated manually that is :(
I have used a similar dim-table approach to restrict the possible field values to choose from. In your example a user can choose for region North, South, East and West. Assume I only want users to be able to choose between North and South. If one reduces the dim-table to only these two options, you get what you want, plus an extra blank-option holding the data for all other regions. This was quite unexpected and I'm wondering why this is. Would you have a clue? And is it possible to suppress the blank-category?
Keep up the lovely videos and blogs, highly appreciated.
reply
Hi Mynda,
I actually ran into this problem today at work (Office 2016) using power pivot based data. After having created the dim-table and replacing the relevant field from the original table with the similar field from the dim-table everything worked like a charm....however, my report based on several getpivotdata-functions (through VBA) throwed an object-error...due to the field names in the getpivotdata-functions still referring to the old table field, i.e. these had to be updated as well to refer to the field name of the new dim-table, updated manually that is :(
I have used a similar dim-table approach to restrict the possible field values to choose from. In your example a user can choose for region North, South, East and West. Assume I only want users to be able to choose between North and South. If one reduces the dim-table to only these two options, you get what you want, plus an extra blank-option holding the data for all other regions. This was quite unexpected and I'm wondering why this is. Would you have a clue? And is it possible to suppress the blank-category?
Keep up the lovely videos and blogs, highly appreciated.
reply
Ian
Many thanks, Mynda. This is very useful. Actually, 'no data' (as Excel calls it) isn't quite right - there -is- data, but it's zero. And often you need to show this, particularly with a bar chart where you need to clearly show days/months that have zero values, as you demonstrate. This has always been one of my criticisms of pivot tables - where the structure/dimensioning jumps about all over the place as you make different choices with the drop-downs/slicers. I always found this irritating! But now I know how to get around this. Thanks, again.
reply
Many thanks, Mynda. This is very useful. Actually, 'no data' (as Excel calls it) isn't quite right - there -is- data, but it's zero. And often you need to show this, particularly with a bar chart where you need to clearly show days/months that have zero values, as you demonstrate. This has always been one of my criticisms of pivot tables - where the structure/dimensioning jumps about all over the place as you make different choices with the drop-downs/slicers. I always found this irritating! But now I know how to get around this. Thanks, again.
reply
apamwamba
Wonderful. for the first case scenario with relatively small unique column items, what i usually do is pivot and unpivot say regions and replace nulls with zero. This trick does not work well for the dates scenario. For the dates case, your solution is perfect!! Thanks for empowering us with valuable knowledge!! Much appreciated...JM from Zambia...
reply
Wonderful. for the first case scenario with relatively small unique column items, what i usually do is pivot and unpivot say regions and replace nulls with zero. This trick does not work well for the dates scenario. For the dates case, your solution is perfect!! Thanks for empowering us with valuable knowledge!! Much appreciated...JM from Zambia...
reply
Wayne
Hi Mynda. Thanks for this further update. So, once the relationship is created, there is an option to show rows with no data. I still like your IF(ISBLANK(SUM())) measure from the first video. But, nice to know about all the options. Thanks for adding this to the lesson :)) Thumbs up!!
reply
Hi Mynda. Thanks for this further update. So, once the relationship is created, there is an option to show rows with no data. I still like your IF(ISBLANK(SUM())) measure from the first video. But, nice to know about all the options. Thanks for adding this to the lesson :)) Thumbs up!!
reply
Brian
Hi Mynda love your videos big fan. I-ve been using power query along more but I can-t seem to get the data field to remain active when I convert my excel table into a power query one by unpivoting the data row. it-s originally in row format like most financial models. Any suggestions?
reply
Hi Mynda love your videos big fan. I-ve been using power query along more but I can-t seem to get the data field to remain active when I convert my excel table into a power query one by unpivoting the data row. it-s originally in row format like most financial models. Any suggestions?
reply
Geert
Yes! This is much better, almost identical to regular pivot tables.
And in the datamodel we're used to working with Date Tables, anyway, so this is a small effort to do.
Thanks for the update. This feature will come in handy! :-)
reply
Yes! This is much better, almost identical to regular pivot tables.
And in the datamodel we're used to working with Date Tables, anyway, so this is a small effort to do.
Thanks for the update. This feature will come in handy! :-)
reply
Бизнес
I made a macro in Excel in VBA, protected it with a 30-character password. In openoffice, my macro opened as if there was no password at all... Please comment on this situation. Thank you.
reply
I made a macro in Excel in VBA, protected it with a 30-character password. In openoffice, my macro opened as if there was no password at all... Please comment on this situation. Thank you.
reply
Wasim
I have data in excel for the year by dates If I need to export or import - based on dates filling the date on cell data should import based on excel cell specified dates
reply
I have data in excel for the year by dates If I need to export or import - based on dates filling the date on cell data should import based on excel cell specified dates
reply
Rodney
Great tip Mynda. Will this work if the list of dates is the weekending date, i.e., 52 wk ending dates? Much of my work is using the wk end instead of daily data.
reply
Great tip Mynda. Will this work if the list of dates is the weekending date, i.e., 52 wk ending dates? Much of my work is using the wk end instead of daily data.
reply
Jason
Perfect! exactly what I needed. I was struggling with the pivot results on the dashboard I had created and was hoping for a solution like this. Thank you!
reply
Perfect! exactly what I needed. I was struggling with the pivot results on the dashboard I had created and was hoping for a solution like this. Thank you!
reply
Add a review, comment
Other channel videos
