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
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
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
RSAT Download
Calvin Wong in Windows Server Insiders on
10 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies