Results 1 to 9 of 9

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

  1. #1
    Neophyte rantanwang's Avatar
    Join Date
    Apr 2021
    Posts
    4
    Articles
    0
    Excel Version
    excel for office 365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    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?
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte rantanwang's Avatar
    Join Date
    Apr 2021
    Posts
    4
    Articles
    0
    Excel Version
    excel for office 365
    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.
    Attached Files Attached Files

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    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"
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Neophyte rantanwang's Avatar
    Join Date
    Apr 2021
    Posts
    4
    Articles
    0
    Excel Version
    excel for office 365
    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

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    Just on my way out, but will respond to all three questions when I get back later this afternoon.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Neophyte rantanwang's Avatar
    Join Date
    Apr 2021
    Posts
    4
    Articles
    0
    Excel Version
    excel for office 365
    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

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,660
    Articles
    0
    Excel Version
    Office 365 Subscription
    Welcome to the Pandora's Box that is PowerQuery!
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •