can a pivot table show my data in the way i need it?

rantanwang

New member
Joined
Apr 12, 2021
Messages
4
Reaction score
0
Points
0
Excel Version(s)
excel for office 365
hi i have an energy readings spreadhseet populated by ms forms responses. i need to get the meters as rows and then the monthly readings as columns to the right but just cant get it to display how i want it to. Any help really appreciated.
 

Attachments

  • Energy Readings 1.xlsx
    224.8 KB · Views: 14
I'm just down the road from you!!!

Can you please mock up a few rows of data showing how you want it to look?
 
Hi Ali thank you so much for responding. I've attached a redacted copy of the actual spreadsheet with comments and a tab with a mock up. Really really appreciate your help.
 

Attachments

  • updated Energy Readings 1.xlsx
    232.6 KB · Views: 14
See if this PowerQuery solution suits your needs.

M Code:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="TBLREADINGS"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Reading Date", type datetime}, {"Completion time", type datetime}, {"Email", type text}, {"Name", type text}, {"Region", type text}, {"Select Site", type text}, {"Select Site2", type text}, {"Select Site3", type text}, {"Select Site4", type text}, {"Site Name", type text}, {"Select Meter", type text}, {"Select Meter2", type any}, {"Select Meter3", type text}, {"Select Meter4", type any}, {"Select Meter5", type text}, {"Select Meter6", type text}, {"Select Meter7", type text}, {"Select Meter8", type any}, {"Select Meter9", type text}, {"Select Meter10", type any}, {"Select Meter11", type text}, {"Select Meter12", type text}, {"Select Meter13", type text}, {"Select Meter14", type text}, {"Select Meter15", type text}, {"Select Meter16", type text}, {"Select Meter17", type text}, {"Select Meter18", type any}, {"Select Meter19", type text}, {"Select Meter20", type text}, {"Select Meter21", type text}, {"Select Meter22", type text}, {"Select Meter23", type text}, {"Select Meter24", type text}, {"Select Meter25", type any}, {"Select Meter26", type text}, {"Select Meter27", type any}, {"Select Meter28", type any}, {"Select Meter29", type text}, {"Select Meter30", type text}, {"Select Meter31", type any}, {"Meter Name ", type text}, {"Utility Type", type text}, {"Month", type text}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type any}, {"Jun", type any}, {"Jul", type any}, {"Aug", type any}, {"Sep", type any}, {"Oct", type any}, {"Nov", type any}, {"Dec", type any}, {"Comments ", type text}, {"Photots", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Month", "Select Meter", "Select Meter2", "Select Meter3", "Select Meter4", "Select Meter5", "Select Meter6", "Select Meter7", "Select Meter8", "Select Meter9", "Select Meter10", "Select Meter11", "Select Meter12", "Select Meter13", "Select Meter14", "Select Meter15", "Select Meter16", "Select Meter17", "Select Meter18", "Select Meter19", "Select Meter20", "Select Meter21", "Select Meter22", "Select Meter23", "Select Meter24", "Select Meter25", "Select Meter26", "Select Meter27", "Select Meter28", "Select Meter29", "Select Meter30", "Select Meter31", "Select Site", "Select Site2", "Select Site3", "Select Site4", "ID", "Reading Date", "Completion time", "Email", "Name"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Region", "Site Name", "Meter Name ", "Utility Type"}, {{"Jan", each List.Sum([Jan]), type nullable number}, {"Feb", each List.Sum([Feb]), type nullable number}, {"Mar", each List.Sum([Mar]), type nullable number}, {"Apr", each List.Sum([Apr]), type nullable number}, {"May", each List.Sum([May]), type any}, {"Jun", each List.Sum([Jun]), type any}, {"Jul", each List.Sum([Jul]), type any}, {"Aug", each List.Sum([Aug]), type any}, {"Sep", each List.Sum([Sep]), type any}, {"Oct", each List.Sum([Oct]), type any}, {"Nov", each List.Sum([Nov]), type any}, {"Dec", each List.Sum([Dec]), type any}})
in
    #"Grouped Rows"
 

Attachments

  • PQ Energy Readings AliGW.xlsx
    245 KB · Views: 13
AliGW thats fantastic!!

i have a couple of questions
  1. how do i add the code to the master spreadsheet
  2. easiest way to have blank cells show a zero
  3. can i build a dashboard from the TBLREADINGS data

apologies for being so naive.

Dave
 
Just on my way out, but will respond to all three questions when I get back later this afternoon. :)
 
how do i add the code to the master spreadsheet

Well, of course it depends, but as your master workbook must contain TBLREADINGS, it should be as easy as creating a blank query and copying in my code. If you need me to explain how, I can.

easiest way to have blank cells show a zero

You can find and replace blanks with a zero from the Transform ribbon in the query editor - Replace Values (null and 0 in the two boxes).

can i build a dashboard from the TBLREADINGS data

Yes, because PowerQuery can help you to present the data in lots of different ways.It depends on what you want, ultimately.
 
Thank you so much, figured out how to load into editor and added extra columns to calculate consumption and am now creating a dashboard with slicers. You're a star :)
 
Back
Top