Pivot Charting on realtime data (having gaps)

[TheChatty]

Hi,

I want to display gas prices from one or multiple stations changing during the day and compare to other days. Gas prices are delivered as CSV with one entry per change - if there is no update for several hours there is no entry for it. To draw a correct XY lline chart I had to double the data to account for the steady price until the next update happens:
source2adjusted2chart.png

What I want to achieve is a pivot chart from the source data where I can select one or multiple stations (id), one or several hours, days, weeks, ... I could mock this chart by blowing up the source data and generate a price value for each point in time. To keep Excel in a usable state I only created 108 (=(24-6) * 6) points per day. An exact chart with 1080 (=(24-6)*60) points is desired. And not having to blow up the data is desired as well. The mock up looks like:
mockup.png

My achievements with lines for several days for one station in the XY chart as well as in the pivot chart can be found here:
gasdb.xlsx

[dflak]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TheChatty]

I rephrased the task and uploaded my example. It contains more than a week worth of data. To make the XY line chart more readible you can filter the table next to it to just a few days (e.g. to show Friday only). Same applies to the pivot chart. I hope for a clever calculated field or something.

[dflak]

Logically, I cannot see a way to do this without filling in the missing data. This can be accomplished with VBA. If you wish to preserve the original table, then you can write the data to a temporary table and plot off of that. In other words, use vb to create the table in the tab you have labeled as Mock.

If you want the data to be plotted, it has to be there. The best other thing you can do is use the option to "connect the dots" which will connect the before and after points with a ramp instead of a step.