Excel - Sorting Alphabetic Months in a Pivot Table | Excel Hacks & Solutions - Episode 2624
HTML-код
- Опубликовано: 4 июл 2024
- Microsoft Excel Tutorial: Sorting months in a pivot table when they are alphabetic.
I met people who are downloading data from Oracle through Analysis Services. Their dates are coming in as text in the format of Sep-20 for September 2020. When they create pivot tables, the months are alphabetic instead of sequential.
To download the workbook from today: www.mrexcel.com/youtube/BpgjO...
Welcome to episode 2624 of MrExcel's Netcast, where we explore all things Excel. In today's video, we will be discussing a common issue faced by many Excel users - Oracle sending dates as text in the format MMM-YY. This can cause chaos when creating pivot tables, as the dates are sorted alphabetically instead of chronologically. But don't worry, we have some Excel hacks that will help you solve this problem in no time.
In this video, I will be showing you two ways to solve this issue. The first method is a bit convoluted, but it only needs to be done once and never again. The second method is super easy, but unfortunately, it didn't work for me. So, I am calling out to all the Power Query and Formula experts out there to share their solutions in the comments below. Let's work together to find the best and most efficient way to handle this problem.
So, let's dive into the issue. We have a refreshable query from Oracle that sends dates in the format SEP-23. This is causing problems when creating pivot tables, as the dates are not in the correct chronological order. To solve this, we will be using a trick that was shown to me by Sam Radakovitz many years ago. We will create a custom list of dates and use a formula to convert the text dates into the desired format. However, there is a catch - custom lists do not allow formulas. But don't worry, we have a workaround for that too. Just follow along with the video and you'll have your dates sorted in no time.
But what if your data is not in Excel and is coming from SQL Server Analysis Services? Don't worry, we have a solution for that too. We will be using the Data Model and a few extra steps to get the dates in the correct order. And for those of you who are wondering how far back you can go with this solution, the answer is three years. But if you're young and have a long Excel career ahead of you, make sure to put a reminder in your calendar for January 2041 to refresh your memory on how to do this.
I hope you found this video helpful and learned some new Excel hacks. If you did, don't forget to hit the like button and subscribe to our channel for more Excel tips and tricks. Also, make sure to ring the bell icon to get notified every time we upload a new video. And as always, feel free to leave your questions and comments down below. Thank you for watching and we'll see you in the next episode of MrExcel's Netcast.
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
There are two ways to solve it:
1) A convolunted method from Sam Radakovitz that you only have to do once
2) A simple method that you will have to do 1000 times a year.
In this episode, I show Sam Rad's method of setting up a custom list with the text months in the correct sequence. If you use this method with a pivot cache pivot table (any pivot table where the data is a regular Excel range), the months will start sorting correctly.
But if your data is a cube or coming from external sources, then you have an extra eight steps in each pivot table to correct the month sequence. However, this is still faster than manually rearranging fields in the pivot table.
In the outtake, I show my fastest method for changing text months in Excel to real dates and ask if you have anything faster.
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#pivottable
Table of Contents
(0:00) Pre-roll ask for help
(0:36) Problem Statement: Text Dates show up as MMM-YY
(1:01) Pivot table is sorting months alphabetically
(1:17) Setting up a text custom list of months
(1:39) Converting dates to text using TEXT()
(2:06) Importing the custom list
(2:49) Regular pivot table automatically works
(3:12) If pivot table based on external data, does not work
(3:37) Sorting pivot table on custom list if based on data model
(4:03) Wrap up
(4:16) Outtake: Convert with Text to Columns
(5:08) Outtake: Convert with Ctrl+H & then Text to Columns
(5:48) Another Ctrl+H solution that works faster
(6:11) Power Query Column from Examples
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
Hi Mr.Excel,
Here's a 1-step (after Source) solution in PQ-M:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
SetDateRight = Table.TransformColumns(Source,{"Original", each Date.From("01-"&_, "en-BE"), type date})
in
SetDateRight
PS: you probably don't need the locale, but I just wanted to make sure... 🙂
Hi @GeertDelmulle
I like Date.FromText when the source has a certain format.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
SetDateRight = Table.TransformColumns(Source,{"Original", each Date.FromText(_, [Format="MMM-yy"]), type date})
in
SetDateRight
Hi @@suatmozgur,
That is actually a good one: playing directly on the input format - no need to worry about the locale. Beautiful! :-)
I run into this all the time in my current role, so with Power Query I simply add a "1" in the appropriate format (and usually do "End of Month" once it's transformed to a date). In this case, add "01-" to the beginning of the column, and then transform. After loading into PQ and then removing the automatic "Changed Type" step, you can use the Transform/Add Prefix command from the menu:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Prefix" = Table.TransformColumns(Source, {{"Original", each "01-" & _, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Prefix",{{"Original", type date}})
in
#"Changed Type"
I put a list somewhere on the side or in another sheet with Jan, Feb, Mar etc in one column and the numbers 1-12 in the next one. Autofill would do that in seconds. And then made the following function to convert the text (in my case B1) into numbers =DATE(YEAR(TODAY()),XLOOKUP(LEFT(B1,3);Months[Month];Months[Month'#]);1). But in real life I'd use Power Query. Thank you for this nice challenge.
A simple solution with a formula. If the MMM-YY text value is in cell A2, this will convert it to a date:
=DATEVALUE(MATCH(LEFT(A2,3),TEXT(DATE(2003,SEQUENCE(12,1,1,1),1),"MMM"),0)&"/01/"&RIGHT(A2,2))
In Power Query, right-click the column header - Select 'Change Type' - 'Using Locale...' - in the dialog box, set the Data Type to 'Date' and set the Locale to 'English (United Kingdom)' - click 'OK'
I was also going to suggest this. Seems power query operates using the correct date format by default, rather than the outmoded MDY 😁
I am exposed to this problem quite a bit, my solution is quite simple..
Enter 1 in a certain cell outside of a database, copy it, mark all the date cells - right mouse button - special paste and select the action in 'multiply'
And it will work out perfectly! 🤩🤩
Wow! That’s great. Never thought of this.
Very interesting, Bill. I notice that at 2:50 you can just select any cell in the PT and press CTRL* then Del to get rid of it.
Interesting. When I use Alt+DEF, it works! Perhaps the British DD/MM/YYYY is just better? Maybe you should consider contacting the President and asking him to change the format from Monday? Also, maybe try metric at the same time.
As to the main solution, I would advise the Oracle user (....not to use Oracle!) to separate their data from their reports at all times. Then they can just download their Oracle data to a workbook or CSV (CSV is quicker in PQ) and have a report workbook with a Power Query connection to create their various pivot tables and so forth. Then it will just be a simple download, then refresh cycle each month/period. Power query will transform the data in exactly the same way each time so you just fix once. It also saves having raw data in the workbook, which usually just looks ugly (in my opinion!!!).
Using UK locale does the trick. M-code is:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Result = Table.TransformColumnTypes(Source, {{"Original", type date}}, "en-GB")
in
Result
Dear Bill,
My solution for listing monthly dates from 2020 to 2040, in text format:
=PROPER(TEXT(DATE(2020,SEQUENCE(21*12),1),"mmm-yy")) 🤗
=let(src,a1:a100,
SORTBY(src,DATEVALUE("01-"&TEXTBEFORE(src,"-")&"-20"&TEXTAFTER(src,"-"))))
Just sorts the text strings according to their date equivalents.
I’m pretty sure that all your words here, Stem from the bizarre American dating system… 😂
Try =DATEVALUE(1&A2)