•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Unable to Group by Month on Pivot Tables.
Our take
If you're facing challenges with grouping dates by month in a pivot table, you're not alone. The issue often arises when exporting directly from Power Query to a pivot table, as this can lead to limitations in date formatting and grouping options. While exporting to a table first allows for seamless grouping, the direct approach may conflict with how Excel interprets date data, particularly when utilizing the data model.
I have a weird situation that I can't find a solution to online. If I export my Power Query* query into a table then make a pivot table out of that, the option to group by months automatically comes out. But if I export my query directly into pivot table, Excel won't let me group my dates into months - group field, group selection, all those don't work. Any idea why this is happening? It can't be the format cause query>table>pivot table works. The second pivot table is loaded into the data model.* Could this be the reason why?
*edited
Edit2: using DATEVALUE() on a Pivot Table date gives a #VALUE error. Is there any info how Pivot Table formats dates? Wrapping the date in a TEXT(,"m-dd-yyyy") fixes it.
The left indent on the right PT could be a clue but I don't know how to interpret that.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Table top bar date filter doesn't update in chronological orderI have a an excel file which I had been building from scratch from a downloaded report every month and am trying to better automate since I do the same type of data filtering each month. I decided to create a table with the data that is linked to a pivot table which sorts and filters it into the format that I need as long as I manually adjust a couple things. However, whenever I add new data to the table, the date filter at the top of the pivot table does not show in chronological order. (Accountant working on April data and need to remove anything that was posted in May.) Theoretically I could use the timeline feature, but I'd rather not. I have resorted the source table in chronological order; I have removed the filter and re-added it in various formats. I've changed the data source, played with various settings. Is there a quick "fix" for this without manually creating the pivot table each time? I have several of these pivot tables on a sheet extracting different data sets sourced from the same table. In one of them, I need to see ONLY the items with a May date, so having to scroll through to check or uncheck the correct dates if they are out of order could lead to errors. Additionally, assuming that this can be corrected. Is there a way for an excel sheet to automatically add or remove row (by hiding or deleting, don't care which) as a pivot table gets longer or shorter so I don't have to guess how many rows I need to add before it cuts into other data below it? Sometimes I need 70+ rows and others only 30. I'm sure there's an overall better way to do what I'm doing. Thanks in advance. https://preview.redd.it/56optogx0qzg1.png?width=406&format=png&auto=webp&s=551180d29effbcfedb7038f60612e33f4e897d3d submitted by /u/inez6453 [link] [comments]
- problem with pivot table date columnsi had full dates, then i made them only mm/yyy type, but when put in the pivot table they still count the same month as different columns because they still count the days in, even though i changed it to mmm/yyy https://preview.redd.it/jds1lkbg9xtg1.png?width=1088&format=png&auto=webp&s=869a4bf431c54e6e9603d3cb25fe2bc53981b64b https://preview.redd.it/l8px4ijk9xtg1.png?width=164&format=png&auto=webp&s=01a8e47a9b24ba184aa097af2c212e453ec1c4b2 thats the view from the forula tab, idk why they still count the days, how do i solve this? https://preview.redd.it/wtsz4wrl9xtg1.png?width=283&format=png&auto=webp&s=5cd03a6a328ee992de70b209e429441f11294188 submitted by /u/PurpleDurian7220 [link] [comments]
- Pivot Table Show Details not working, any idea?Hi there, I am facing with a quite unique issue I think. There are some pivots in an Excel file that we use for internal reports for the AP team. The issue is that for the pivots there are several filters applied on, like Intercompany filter or "is the amount negative" etc. but there is the most important one is the Vendor filter, there are numerous vendors excluded, and I was like okay let me just recreate the pivot from the ground, but that would take a bit too much due to the vendor filter. The main problem is that the show details is not working, if I execute a double click on the pivot's total cells then it is loading for a 0.1 second and then nothing happens. If I try it after I close and reopen the file, then it loads for 2 seconds, with the botom right text saying "Reading Data" but then nothing, same results. It is a connection based pivot table, I tried to copy and paste into a new sheet the Pivot, didn't work, I tried to Save & Repair the file, it didn't work. Any idea? submitted by /u/Strange_Cell1142 [link] [comments]
- Pivot table french date 'month' format in grouped datesHi Excel, I want to change the grouped dates month format from french to english. What is strange is that my excel is in english except this. Any idea how I can change the "Janv, Févr etc." to "Jan, Feb, etc."? Any help appreciated! https://preview.redd.it/w5uyg7nwhh0h1.png?width=516&format=png&auto=webp&s=9ad0cd80c150af239e1f830826c6f04fa61106a4 submitted by /u/jacqueschirekt [link] [comments]
Tagged with
#Excel alternatives for data analysis#Excel compatibility#Excel alternatives#rows.com#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#Pivot Table#Power Query#group by months#Excel#dates#group field