+ Reply to Thread
Results 1 to 7 of 7

Power Query - Pivot Table not able to group timestamps into hour/days/months/years

  1. #1
    Registered User
    Join Date
    09-05-2019
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365
    Posts
    94

    Power Query - Pivot Table not able to group timestamps into hour/days/months/years

    Hello

    I am using Power Query to organize, clean and sort data that have attached timestamps. More often than not, when I'm transforming the data into a Pivot Table, I'm unable to group my date timestamps into my desired groups of hours/days/months. However, this happens randomly, so I'm not sure if it is something that I'm doing in Power Query that messes things up.

    I have attached my sample zipped folder.

    Thanks

    Edit1: I have posted a similar question on another forum, ExcelGuru. Here's the link: https://www.excelguru.ca/forums/show...date-timestamp
    Attached Files Attached Files
    Last edited by dcwan; 08-12-2020 at 11:20 AM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,623

    Re: Power Query - Pivot Table not able to group timestamps into hour/days/months/years

    What format is date column in the csv file?

    dd-mm-yyyy h:mm? Or is it mm-dd-yyyy h:mm?

    My guess is that it's in "dd-mm-yyyy h:mm". Power Query when using implicit conversion, assumes US standard (i.e. mm-dd-yyyy).

    When reading date/time from csv. You need to be careful what culture was used to generate the file. And instead of relying on implicit conversion you need explicit conversion.

    In your case... when changing type for the datetime (timestamp) column, I'd go to bottom of the menu and choose "Using Locale..." Date type to Date/Time. Locale to English (United Kingdom).

    However, it seems your file is corrupted. As no matter what I do, I can't add grouping in Pivot (though you can add calculated columns to serve as grouping).

    When I used above steps and recreated your model, it grouped without issue.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    09-05-2019
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Power Query - Pivot Table not able to group timestamps into hour/days/months/years

    What are the possible reasons that the file could have gotten corrupted? I don't think i did anything strange within the Power Query steps in order to produce a corrupted file.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,623

    Re: Power Query - Pivot Table not able to group timestamps into hour/days/months/years

    Can't say for sure. Though I suspect issue with culture mismatch as I mentioned above.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,273

    Re: Power Query - Pivot Table not able to group timestamps into hour/days/months/years

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  6. #6
    Registered User
    Join Date
    09-05-2019
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Power Query - Pivot Table not able to group timestamps into hour/days/months/years

    Edited in my original post. Usually I don't do this, but I couldn't find a dedicated forum area for Power Query until I saw a Power Query question in the "Office365" area and by that time, I already posted on another site.

    Just forgot to update amidst all the before suggested changes.
    Last edited by dcwan; 08-12-2020 at 11:24 AM.

  7. #7
    Registered User
    Join Date
    09-05-2019
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Power Query - Pivot Table not able to group timestamps into hour/days/months/years

    Well, I have tried the new steps, ensuring the "Add to Data Model" is unchecked, and it seems to be working. I will be closing this.

    thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 04:09 PM
  2. Power Query and Power Pivot Table
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 06:17 AM
  3. Power Query and Power Pivot Table !!
    By haitham.shop in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2018, 06:15 AM
  4. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-06-2018, 05:59 AM
  5. pivot table: comparing months in different years
    By ammartino44 in forum Excel General
    Replies: 0
    Last Post: 10-17-2014, 02:02 AM
  6. Replies: 3
    Last Post: 07-07-2012, 02:43 AM
  7. pivot table: years instead of months
    By bestox in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-26-2009, 08:12 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1