Log In
Register
Home
Active Member
September 19, 2021
Offline
I'm aware that dates and pivot charts don't always get along but I'm hoping someone will have a new suggestion.
I have a large number of data files with soil moisture values over time, for multiple sites and depths. I also have a reference file with information about the placement and type of each sensor. I am using a power query to combine the data files and reference information, then the Data Model to get everything the was I need it. I want to be able to use pivot chart functionality (slicers and a timeline) to visualise the data. It's not possible to have a pivot chart unless the data are summarised in some way so I added a column in the power query that rounds the measurement date-times to the nearest half hour. My pivot chart averages the moisture readings for each rounded date-time so I can get the curves I'm wanting. The curves look good.
But, I cannot get the x-axis to look reasonable. It insists on showing the full date and time and it won't allow me to set a format.
I understand that the usual procedure is to access the field settings via the pivot table where I should find a Number Format button. However, there is no such button displayed. I'm told that can happen when there are blanks or non-date-time values in the column - but I don't have that, it's all date-times.
Any ideas would be gratefully received.
See Answer
July 16, 2010
Offline
Hi Chris,
Welcome to our forum!
In Power Pivot you can set the number format for a field in the Power Pivot model on the Home tab of the ribbon.
Mynda
Active Member
September 19, 2021
Offline
Hi Mynda, thanks for the welcome and the reply.
I was able to change the date column in the power pivot data model to display only the date part of the date-time data. However, the corresponding x-axis in the pivot chart stubbornly continues to include the time as well as the date. I refreshed everything, and tried creating a new pivot chart directly from the data model window but still no success.
Chris
July 16, 2010
Offline
Hi Chris,
When you say 'change the date column in the Power Pivot data model to display only the date part' do you mean with the formatting?
Have you tried setting the formatting for the chart axis?
You could try changing the data type in Power Pivot to date only.
Mynda
Active Member
September 19, 2021
Offline
Hi Mynda, re your questions and suggestion:
'When you say 'change the date column in the Power Pivot data model to display only the date part' do you mean with the formatting?' Yes, I changed the formatting for the field (column) within the data model, using the home tab of the Power Pivot ribbon as you suggested. It now displays as date only. The time component is still there but you don’t see it unless you select a cell.
Have you tried setting the formatting for the chart axis? Yes, over and over again, hoping for a different result (I'm aware what that implies about my mental state!) The Number section, within the Format Axis panel that pops up for the Pivot Chart, doesn't change the chart format at all. But it does work as expected for the y-axis. I've left it set to 'Linked to source'.
You could try changing the data type in Power Pivot to date only. The date type in Power Pivot is date. (The choices are Test, Date or Decimal Number.) The formatting I've set is yyyy-mm-dd, which is what I'd like to see on my Pivot Chart x-axis. (Actually, I want mm-dd but that's not on the list.)
My searching suggests that the most usually effective way to format a date/time x-axis on a Pivot Chart is to adjust the field settings in the associated Pivot Table. But the Number Format button doesn't appear in the field settings window. I'm told that happens when there are non-date entries or blanks somewhere in the column but I don't have those - it's all date/times.
Thanks for any help!
Chris
July 16, 2010
Offline
Hi Chris,
Are you able to share your file or a sample file that illustrates the issue? It's difficult to troubleshoot without seeing the data.
Mynda
Active Member
September 19, 2021
Offline
Hi Mynda,
Sorry for the slow reply. I've attached a pared-down version of what I'm working on. More information can be found on the 'notes' sheet in the file.
Thanks!
July 16, 2010
Offline
Hi Chris,
Thanks for sharing your file. If you want the chart to show every data point at the time level of detail, then the best you can do is nest the axis labels to show both months and time. You cannot change the axis more than this. See chart in file attached on sheet PC.
However, if you're happy to aggregate the data into days, then you can add a column to your source data that extracts the mm-dd values from the date time column and then use that in your chart. See file attached on sheet PT (2).
Mynda
Answers Post
Active Member
September 19, 2021
Offline
Hi Mynda,
Thanks for looking at this. Sadly, those two options match what I had arrived at before I came searching for help! Aggregation to days sacrifices too much resolution so we'll probably just have to live with the full date-times. A bit frustrating but now at least I'll stop wondering if I could do better.
I also expect to be using this helpful guide https://www.myonlinetraininghu.....s#comments and will give credit in my documentation - thanks again,
Chris
July 16, 2010
Offline
Hi Chris,
Another option is to reference the PivotTable with formulas to recreate the source data where you can apply the number format you want. You can then insert a regular chart referencing this data.
If you expect the PivotTable to grow, you can extend your formulas past the end of the PivotTable to allow for growth and then use a dynamic named range for your chart to reference.
See file attached on PT sheet with an example using one port.
Mynda
1 Guest(s)
