Excel Pivot Tables - "locking" column headings...
Discussion
My Google-fu isn't on form today so asking the gathered ranks here. I suspect the answer is 'no', but here goes....
I need to create a pivot table from a sizeable chunk of data, but want the column headings to remain consistent.
The columns are based on values in a cell - say month names - but if the data doesn't happen to have any records with a particular month in it, the column falls away in the pivot. This is, I think, as pivots are designed to work...but it's annoying for the purpose I need
Is there any way to get a pivot table not to do this, and to work with an, in essence, static set of columns based on universally allowable values that the dataset could include (e.g. Jan-Dec)?
Using a pivot is useful as there are other dimensions in the data that potentially need to be dissected on. So I don't want to lose that functionality of a pivot.
I'm suspecting that what I'm needing to do is stretching Excel to the limit and what I really need is something like PowerBI. But thought I'd ask
I need to create a pivot table from a sizeable chunk of data, but want the column headings to remain consistent.
The columns are based on values in a cell - say month names - but if the data doesn't happen to have any records with a particular month in it, the column falls away in the pivot. This is, I think, as pivots are designed to work...but it's annoying for the purpose I need
Is there any way to get a pivot table not to do this, and to work with an, in essence, static set of columns based on universally allowable values that the dataset could include (e.g. Jan-Dec)?
Using a pivot is useful as there are other dimensions in the data that potentially need to be dissected on. So I don't want to lose that functionality of a pivot.
I'm suspecting that what I'm needing to do is stretching Excel to the limit and what I really need is something like PowerBI. But thought I'd ask
Load the source data into Power Query (Data Menu - Get Data) to sort it out so that it works for you in the pivot. Then load the data to the Data Model. Create your pivot tables from the Data Model.
Other than a few minor bits and pieces, if you do it that way the only difference between Excel and PowerBI for a non-cloud data source is the data presentation stuff. The nuts and bolts are the same.
Depending on your source data, you may want to get data from a specified folder and just drop new data in there, Power Query will grab all the files and build them into a single table, leaving you with a spreadsheet that updates itself automatically.
If you have to frig the data a little to force the data to work how you ant, Power Query is the place to do it.
I *think* what you want will work if you create a date table (google this) in Power Query and add a relationship between the date table and the data table. Then add a second date table which contains the pivot table headings you want jan-dec and the individual dates. Add a relationship to the date table from this. In your Pivot, use your data in Values and Columns, but the Jan-Dec values from the second date table. I think that the way filtering works between the tables will leave you with your empty cells, thought I'd need to test it to be sure.
Other than a few minor bits and pieces, if you do it that way the only difference between Excel and PowerBI for a non-cloud data source is the data presentation stuff. The nuts and bolts are the same.
Depending on your source data, you may want to get data from a specified folder and just drop new data in there, Power Query will grab all the files and build them into a single table, leaving you with a spreadsheet that updates itself automatically.
If you have to frig the data a little to force the data to work how you ant, Power Query is the place to do it.
I *think* what you want will work if you create a date table (google this) in Power Query and add a relationship between the date table and the data table. Then add a second date table which contains the pivot table headings you want jan-dec and the individual dates. Add a relationship to the date table from this. In your Pivot, use your data in Values and Columns, but the Jan-Dec values from the second date table. I think that the way filtering works between the tables will leave you with your empty cells, thought I'd need to test it to be sure.
Edited by paulrockliffe on Wednesday 10th March 13:07
You want to do what Paul has suggested by using the Data Model route. Then create an extra Table with your months in there (use a second column with 1 to 12 in the sort order you want) and load this into the Data Model too.
Link the two data sets together via the month, but use the month from the date table in your pivot tables/charts. When in the data model select the data table data and sort the month name by the second column and that will fix the order they will appear in - for example, my work uses the financial year Apr to Mar so I can start my sort column as 1 for Apr, 2 for May, down to 12 for Mar.
This youtube video (somewhere around the 15mins mark) shows how what we mean: https://www.youtube.com/watch?v=CjSm5sI3z8o&t=... Edited to add, he has his sort column already in his data set, whereas we're suggesting a new table, but how to set up the sort is the same.
Link the two data sets together via the month, but use the month from the date table in your pivot tables/charts. When in the data model select the data table data and sort the month name by the second column and that will fix the order they will appear in - for example, my work uses the financial year Apr to Mar so I can start my sort column as 1 for Apr, 2 for May, down to 12 for Mar.
This youtube video (somewhere around the 15mins mark) shows how what we mean: https://www.youtube.com/watch?v=CjSm5sI3z8o&t=... Edited to add, he has his sort column already in his data set, whereas we're suggesting a new table, but how to set up the sort is the same.
Edited by Planet Claire on Wednesday 10th March 15:24
Planet Claire said:
You want to do what Paul has suggested by using the Data Model route. Then create an extra Table with your months in there (use a second column with 1 to 12 in the sort order you want) and load this into the Data Model too.
Link the two data sets together via the month, but use the month from the date table in your pivot tables/charts. When in the data model select the data table data and sort the month name by the second column and that will fix the order they will appear in - for example, my work uses the financial year Apr to Mar so I can start my sort column as 1 for Apr, 2 for May, down to 12 for Mar.
This youtube video (somewhere around the 15mins mark) shows how what we mean: https://www.youtube.com/watch?v=CjSm5sI3z8o&t=... Edited to add, he has his sort column already in his data set, whereas we're suggesting a new table, but how to set up the sort is the same.
We're in the same ball park, but I think the OP's issue is that if there is no data for a particular month the Pivot will drop it from the Pivot Table. Usually that's what you want, but on a calendar view the fact that there's no data is probably noteworthy and it it's helpful aesthetically to layout multiple pivots next to each other that can't then overlap on a refresh and stop the data updating.Link the two data sets together via the month, but use the month from the date table in your pivot tables/charts. When in the data model select the data table data and sort the month name by the second column and that will fix the order they will appear in - for example, my work uses the financial year Apr to Mar so I can start my sort column as 1 for Apr, 2 for May, down to 12 for Mar.
This youtube video (somewhere around the 15mins mark) shows how what we mean: https://www.youtube.com/watch?v=CjSm5sI3z8o&t=... Edited to add, he has his sort column already in his data set, whereas we're suggesting a new table, but how to set up the sort is the same.
Edited by Planet Claire on Wednesday 10th March 15:24
The solution I've suggested relies on there being the date table between the table with the date that's used in and the table with the data in. In Excel there's no option to cross-filter via the date table, so putting the months in will force one column per month, data will drop in nicely, but empty columns will be retained. I think anyway, I'm only 95% that it works.
The sorting issue is that Excel will want to sort columns in alphabetical order. The best way to get around this is to add a Month-Sort column in your date table (though in the OPS example it needs to be in the third table) which is basically 01 for Jan, 02 for Feb etc. In Power Pivot you can assign a sort by column to any column in the data model, so you specify that your Month column is sorted by the month-sort column.
I think it's sqlbi.com that has a really powerful example DAX code that you can simply drop into a Calculated Table to give you all these columns over a specified date range to create your date table. For your example, there's a load of columns for Financial Years, so you can work across both calendars without any messing about. Always link on the actual date rather than week or a month as although it will probably work, there's no benefit doing it that way and there's circumstances where you can really screw your numbers up because filters will work differently. More of an issue in PowerBI if you end up cross-filtering without resorting to DAX.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff