Simplify Display of Data in Pivot Chart?

Hi,

I have recorded temperature data and want to make a line graph of this data with power pivot.

The issue I'm having is, I want to show a high resolution of the data (say 24 readings per day), but I only want the days to show up in the X axis, not the hours. Having 24 hours squeezed above each day under the axis is quite unsightly.

The example file attached shows how it's currently set up. Is there a way to somehow hide the time so that only the day shows up?

Apply a custom number format of ;;; to the Time field in the pivot table.

Not that I can think of. Do you have to use a Power Pivot table?

I have 35k + rows of data that will eventually grow to 100k + rows, so I would prefer to use power pivot if possible.

EDIT: did my math wrong, it will actually grow to 500k rows of data. So yeah, I definitely prefer to use power pivot. If there isn't a way to remove those numbers in a power pivot chart, maybe I just take a screenshot from that section of a properly formatted pivot table, delete the horizontal axis on the real chart, and past the image over it. It's a bit tacky, but it's a weekly report where all the charts stay in the same place all year.

Unless you need DAX, you could simply use Power Query to load the data (connection only) and build a normal pivot table off that.

What are the key steps/elements of the entire data analysis/pivot/power pivot process? What is the pivot/power pivot table doing that is absent or problematic in the source data? In the example in post 1, it appears that the source data (columns B:D) and the pivot data (columns F:H) are essentially identical. The pivot/power pivot step did not really do anything to the data other than "combine" weekdays into "groups".

I want to show a high resolution of the data (say 24 readings per day), but I only want the days to show up in the X axis, not the hours.

I look at this description, and I think that an XY scatter chart would have no difficulty with this -- if the source data included some kind of date+time stamp column. An XY scatter chart could easily divide the horizontal axis into days while properly positioning each data point at the correct horizontal position for its date+time value. A pivot chart cannot be an XY scatter chart (not sure why), but, for the example, it would not matter if the XY scatter chart were based on the pivot table or the source data.

To give it something concrete, I might enter suitable date+time values in column A. I could enter 1.25, 1.5, 1.75, 2.25, 2.5, 2.75, etc. into column A (where the numbers 1 to 7 represent the days of the week Sunday through Saturday, and the fractions represent 6 AM, noon, 6 PM for the time of day of the readings). Then use columns A and D to create an XY scatter chart. Format the horizontal axis to have a tick mark every day (and/or any other formatting you want) and to have min and max of 1 and 8, and you're done.

Obviously, I'm reading a lot into the example file. As long as the pivot/power pivot step is doing nothing more than copying/filtering the source data, then I think something using an XY scatter chart on the source data might work better and bypass the need for the pivot/power pivot step.

If you have something you like, then go with what is easy for you.

With this example, I could easily using a technique like this (https://peltiertech.com/regular-char...-pivot-tables/ ) to create a scatter chart from the pivot on "gridlines with no axis" -- if you are interested.