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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies