Home

missing rows pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-901510%22%20slang%3D%22en-US%22%3Emissing%20rows%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901510%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20group%20some%20data%20that%20was%20logged%20irregularly%20by%20a%20sensor%20into%20hourly%20intervals.%20I've%20managed%20to%20do%20so%20with%20a%20pivot%20table%2C%20but%20then%20I%20find%20the%20following%20problem%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20the%20data%20does%20not%20contain%20some%20of%20the%20hours%20of%20the%20day%20(see%20pictures)%2C%20the%20pivot%20table%20does%20not%20include%20them.%20I%20would%20like%20every%20day%20to%20have%20the%20same%20number%20of%20rows%20(24)%20from%2000%3A00%20to%2023%3A00.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136041iC5704E8FE4D35081%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Anotaci%C3%B3n%202019-10-09%20103939.png%22%20title%3D%22Anotaci%C3%B3n%202019-10-09%20103939.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20arrange%20the%20table%20differently%2C%20I%20can%20get%20this%20(see%20second%20picture)%20but%20I%20would%20really%20prefer%20to%20have%20it%20arranged%20vertically%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136045i6513495A782757CE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Anotaci%C3%B3n%202019-10-09%20103939k.png%22%20title%3D%22Anotaci%C3%B3n%202019-10-09%20103939k.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-901510%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902493%22%20slang%3D%22en-US%22%3ERe%3A%20missing%20rows%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902493%22%20slang%3D%22en-US%22%3EYou%20could%20do%20this%20with%20Power%20Query%20by%20adding%20a%20table%20which%20contains%20all%20dates%2C%20create%20a%20relationship%20between%20the%20tables%20(date%20table%20and%20data%20table)%20and%20then%20show%20the%20date%20from%20the%20date%20table%20in%20your%20pivot%20with%20the%20option%20to%20show%20rows%20without%20values.%20Are%20you%20familiar%20with%20power%20query%3F%3C%2FLINGO-BODY%3E
jorgeotaegi
Occasional Visitor

Hi,

 

I'm trying to group some data that was logged irregularly by a sensor into hourly intervals. I've managed to do so with a pivot table, but then I find the following problem:

 

Because the data does not contain some of the hours of the day (see pictures), the pivot table does not include them. I would like every day to have the same number of rows (24) from 00:00 to 23:00.

 

Anotación 2019-10-09 103939.png

 

If I arrange the table differently, I can get this (see second picture) but I would really prefer to have it arranged vertically

 

Anotación 2019-10-09 103939k.png

1 Reply
You could do this with Power Query by adding a table which contains all dates, create a relationship between the tables (date table and data table) and then show the date from the date table in your pivot with the option to show rows without values. Are you familiar with power query?
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies