Power BI Tips - How to sort by Month and Year (and best practices)

[Music] hey everybody welcome to another video here at pragmatic works today we're going to be taking a look at something cool some kind of should be simple and that's going to be sorting by year and month now if you've sorted a column in power bi before you know it's a simple process but of course like everything else there are times where this can be a little bit tricky and a little bit more difficult and that's what we're going to cover today but before we go any further my name is mitchell pearson if you like this video make sure to hit that like and subscribe button hit that bell notification so that whenever we drop a new video you get notified right away and my son told me so it must be true that if you subscribe you can have good luck i think there's a time frame associated with this but i don't know that's what all the famous youtubers do now here we go we're going to jump right in and we're going to take a look at sorting our data right so you've probably had to do this before where you were displaying the month name and it was out of order it was alphabetical instead of chronological so you of course selected the column name over here right you clicked on month and my head's probably blocking that you clicked on month and then you went up to column tools sort by column and you said i want to sort it by month number of year and that worked that worked flawlessly that worked awesomely so let's take a look at something a little bit more complex than that the demo that we're going to be looking at today is a couple of different scenarios where month and year is combined together and so i come across this all the time when we're doing training or hackathons or virtual mentoring sessions where we see data that's somewhat like this and customers want to combine columns together put them on a chart and then they don't quite display the way that they want them so let's look at the first one right the very first one that we have is a month year combination now i can get this one correct by simply you'll notice that it's showing all of the januaries here right because it's sorting by month year then it's showing all of the februaries and then all of the marches what we probably want is zero one two thousand five zero one zero two two thousand five zero three two thousand and five this one is actually really easy to solve because of the way that it's formatted i can actually sort this one by the calendar year and it's gonna fix that problem pretty easily right so we can come over here and say look that's month year so i'll grab that month year column right there and then we'll come back up to sort by column and i'll tell it that i want to sort it by calendar year and you'll notice as soon as i sort it by calendar year it is working flawlessly this is perfect the one that we have over here though we got some text in there this one's going to be quite a bit trickier so let's talk about some of those different issues that we have first of all i just showed you a moment ago our month number our month name we've already sorted it by the month number so we know that if we were to bring the month in here if i were to let's say duplicate this table and bring in the month that it's going to sort chronologically not alphabetically we've already fixed that so ideally what i want to see in the month year two is january 2005 february 2005 march 2005 you get the drill so let's try a couple of different things right first let's try to sort this month year two column let's try to sort that also by the year so i'll come over here and find month year two select that column by clicking on the name get that nice gray border in the background then we'll come up to sort by column and i'm going to tell it that i want to sort by calendar year just like we did before and unfortunately it is not quite working it's definitely sorting all the months of 2005 but it's sorting it alphabetically ah april august december february right a b c d e f g but we want it to be chronologically we want it to be one two three january february march in the same order that we have over here but so we see that doesn't work right i sorted by calendar year it doesn't work well well mitchell what if we sort it by month number to solve that whole alphabet problem it's a good good question so i'm going to do that again i'll grab month year come up to the top this time i'm going to tell i want to sort it by month number of year and once again it sorts it but it's not exactly what i'm looking for right it's january of 2005 january 2006 january of 2007. what i want is january 2005 february of 2005 march of 2005. so we still have this problem and the problem really comes down to the fact that when you sort by column you can only sort by a single column so i'm trying to sort this by a single column and i'm choosing year i'm choosing month i'm choosing different options but we need the month and the year we need the month and the year and so there's a couple different ways we can do this we could do it in dax or we can do it in power bi first i'm going to do this in dax okay so we'll do it in dax first but as you know from watching any of our videos or taking any of our training we always recommend doing it in the power query editor right to get the best possible compression so i'll show you in dax and then if you want to hold on and wait just a little bit longer i'll show you how to do it in the power query editor so in order to do this in dax and there's going to be many different expressions we can write but i'm going to go over to the data view over here on the left i'm going to make sure i'm on my date table and then on my date table i have my month year 2 here what i want to create is i want to create a combination of my month and my year all right so that could be 200501 200502 200503 or it could be the inverse of that 0 1 2005 right so it could go really either way what i'm going to do is right click on the date table and create a new calculated column so i'll right click and do new column and then we'll call this month year sort right so month year sort equals and then all i need to do is extract the year and the month from my date right so i'm going to do a format function here and then from my date column on my date table i am going to return the year year year year month month and then i'll close that up like that and hit enter and we'll give it just a moment so when i scroll back over to the right we now have this month year sort that we can use and so if you look at the numbers two thousand five zero one two thousand five zero two it should now sort it in the correct order perfect now all we do at this point is go back and you could do it here as well but i'm going to go back to the report view here's my month year 2 go ahead and highlight that and then i'm going to tell it that i want to sort by the month year sort the one that we just created and when i sort by the month year sort it gives me exactly what i want this is awesome so not super difficult but if you don't if you've never done this and you're looking for it right now this is how you would do it now keep in mind there is a better way to do this the better way is to do this in the power query editor so stick with me for just a moment we're going to jump over to the power query editor by going to home then i'm going to click transform data to launch the power query editor and i'll pull that over from the other screen so on my date table i just have a bunch of standard tables from different videos or whatever but on my date table i also want to add that same kind of month year sort here we'll call it monthly or sort two and let me show you how to do this the way that i would do this is really take advantage of the built-in mquery functionality right so instead of trying to write em or instead of trying to figure this out myself what i'm to do is i'm going to go ahead and do i'm going to select the date column go to add columns and then i'm going to do column from examples but i'm going to click the drop down and i'm going to say column from selection from selection means i already selected the date column so as i'm typing in text and you're trying to figure out what i'm doing try to figure out and derive the actual code from that column so if you don't know what column from examples is you definitely want to take a look at this because it's pretty awesome so column from examples from selection right and then i'm going to come all the way over here to the right and what i want to do in this first column is kind of type out what i want the results to be and so the first row is what we're going starting from january 1st going down in order so the first row i'm going to type in 2005 and then i'm gonna type in zero one and then i'm gonna do it again on the next one 2005 being the year zero one being the month and it's going to repeat it all the way down now clearly if you look at this most of the time it won't pick it up for february so if i go to february here you'll notice that for february it's still recognizing let me make sure i got the right one nope gotta go down one more it's still recognizing it's hard coding that zero one so we're gonna fix the zero one to zero two and hit enter again now it's saying zero one zero two let me see i was i wasn't blocking it really all right and then zero three zero so this is working this is perfect so what we just did is we had to give multiple samples to make that add from column example work that happens sometimes we're going to go in and click ok by the way you can also look at the code the ilm query code right here and it's doing a date.2 text year year year year it's adding a zero and then it's doing a date dot two text percent m uh wait a minute i might have to modify that code we'll click ok and then let's see what the results are oh yeah that's not what we want so unfortunately it is always adding a 0 and we don't want that that is not what i expected i would have had to have gone down to like a 10 to fix that so this is unexpected but we're going to fix it anyway so what i'm going to do is this is actually combining these three different string values together i'm going to get rid of this right here in the middle that 0 that it's adding i don't want that and i am going to come over here and we're going to replace that percent with the two-digit month all right and i think so if i can type that in correctly i think that's going to give us what we want so let me hit enter again right there so i'm essentially modifying the code a little bit i'm apologizing because i did not mean to get into this to this degree but you'll see now it's sorting it correctly and we don't get two zero zero five zero one zero we're getting two zero zero five one zero one one so this is perfect this is what we want now we do need to give this a name so we'll do that very quickly here month year two all right month year two that way we know it is the right one we'll hit enter and now remember the reason that we do this oh i created oh that's the one i created for the other one month year sort two i'm sorry month year sort two there we go all right live demos always never going to be perfect now i'm going to come back over here click home close and apply the reason we do this in the power query editor though is because when you do it in the power creator or you do it further upstream it happens before the compression algorithms get run so it gives you the best possibility for the best possible compressions that is a key element that you never want to forget when you're working in power bi now we're sorting this currently right if i go down here and hover over the month year two we are currently sorting this by our month year sort we're going to change that we're going to change that over to the month year sort two and you'll notice when we change it over we still get the exact same results the only benefit now is that we get potentially better compression in our data model and that's it a little quick tip ran into a couple little problems there had to modify some code to make this thing work the way we wanted it but that is it i hope you enjoyed this video as always we'll see you in the next one [Music] you

Channel: Pragmatic Works

Views: 44,953

Keywords: Power BI Tips and Tricks, Power Query Editor, Sorting Data in Power BI, Sort by Year and Month, PragmaticWorks, Mitchell Pearson

Id: KUcLScKYg9I

Length: 12min 34sec (754 seconds)

Published: Thu Dec 23 2021