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

[dcwan]

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

[CK76]

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.

[dcwan]

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.

[CK76]

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

[alansidman]

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.)

[dcwan]

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.

[dcwan]

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