Today, in Episode #1260, Bill investigates Calculated Columns in the PowerPivot grid using DAX. Also, tips to Format your data for a more visually aesthetic result using PowerPivot and Excel 2010 ... from Bill Jelen a.k.a."MrExcel".
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Power Pivot For The Data Analyst, Chapter 6, Calculated Columns.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well in this chapter we're taking a look at how to use the DAX language right in the Power Pivot Grid to add new calculated columns.
Before I do that though just in case you're not familiar with these functions in Excel, let's talk about some great Date Handling functions in Excel.
So, let's say that we have a date up here July 6.
This is 7/6/2010 and I want to break that down at a year, month and day.
Excel gives us 3 functions that make this really, really easy so equal year of that date will give us 2010, equal month of that date will give us 7 for July and equal day of that date will give us just the 6th.
Okay so, those are built-in.
Unfortunately, they give us numbers. Of course numbers are easy to sort.
But, you know it may not be how we want things to appear.
So, lots of times I will use the Text Function.
Text Function, so equal text of that date, press F4 there to lock that down and then say format it, as shown here to the left.
So, if we use the MMM format you see we get Jul for July.
I'll double click to copy this down.
4 M's will actually spell out the month of July.
A single D will give us the day in a single digit for dates 1st through the 9th or two digits for 10th through the 31st.
But if you specify DD, then you get the very nice sort-friendly, two digits all the time.
So instead of 6 you get 06.
3 D's gives you the day of the week abbreviation, 4 D's actually spells out the day of the week and then you can start to get creative.
For example, YY, MMM would give you the year a - and then the two-digit abbreviation or let's try YY-MMM to actually give us the year and the month.
Now, that one's gonna be really really hard to sort properly in Power Pivot, so that one's probably not even practical.
Okay so, let's go and see how we can use that knowledge from Excel in the Power Pivot window so, we have a Date field here and we want to add a new calculated column so we just start with an equal sign and type the Function that you're starting to use so, I'm gonna use the year function you notice as soon as I type Y, they offer a couple of choices here I'll press Tab to choose year and then they want to know which field.
Okay now, hardcore Excel people know that we can use the arrow keys to go back to that field.
Unfortunately, Power Pivot does not support the arrow keys you have to either start to type the field name or just use the mouse to click on it.
I know most hardcore people hate to use the mouse.
But here we are that's it's just that, what we have to do. Click the closing parentheses and very quickly it will give us the Year column for those 1.8 million rows.
Again, bad heading, I wish they would just prompt us for the heading because we're gonna have to change that heading every single time.
I'll call this Year.
All right and I could do similar things for a Month and for a Day using Month and Day Functions.
Again, most functions that we use here in this grid are exactly like the Functions that we use in Excel.
The one clearing difference is the Text Function, which is so commonly used in Excel is not spelled text here is spelled as Format.
So, we'll use equal Format, click on the field that we want, a comma and then in quotes whatever format we would like to use.
So, I'm going to spell it the month completely, that would be 4 M's in quotes, closing quotes, closing parentheses, press enter and let that calculate down.
And again once we get the answer, you have to right-click and change that horrible heading to something a bit more friendly.
All right so, there you have it.
Just a sampling of some of the many DAX Functions that you can use to create new calculations right here in the grid.
Chapter 6 has a complete list of all those DAX Functions and examples of how to use them.
I figure this is the one that you'll run into most often after it this is what we do in the podcast.
All right, well I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Power Pivot For The Data Analyst, Chapter 6, Calculated Columns.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well in this chapter we're taking a look at how to use the DAX language right in the Power Pivot Grid to add new calculated columns.
Before I do that though just in case you're not familiar with these functions in Excel, let's talk about some great Date Handling functions in Excel.
So, let's say that we have a date up here July 6.
This is 7/6/2010 and I want to break that down at a year, month and day.
Excel gives us 3 functions that make this really, really easy so equal year of that date will give us 2010, equal month of that date will give us 7 for July and equal day of that date will give us just the 6th.
Okay so, those are built-in.
Unfortunately, they give us numbers. Of course numbers are easy to sort.
But, you know it may not be how we want things to appear.
So, lots of times I will use the Text Function.
Text Function, so equal text of that date, press F4 there to lock that down and then say format it, as shown here to the left.
So, if we use the MMM format you see we get Jul for July.
I'll double click to copy this down.
4 M's will actually spell out the month of July.
A single D will give us the day in a single digit for dates 1st through the 9th or two digits for 10th through the 31st.
But if you specify DD, then you get the very nice sort-friendly, two digits all the time.
So instead of 6 you get 06.
3 D's gives you the day of the week abbreviation, 4 D's actually spells out the day of the week and then you can start to get creative.
For example, YY, MMM would give you the year a - and then the two-digit abbreviation or let's try YY-MMM to actually give us the year and the month.
Now, that one's gonna be really really hard to sort properly in Power Pivot, so that one's probably not even practical.
Okay so, let's go and see how we can use that knowledge from Excel in the Power Pivot window so, we have a Date field here and we want to add a new calculated column so we just start with an equal sign and type the Function that you're starting to use so, I'm gonna use the year function you notice as soon as I type Y, they offer a couple of choices here I'll press Tab to choose year and then they want to know which field.
Okay now, hardcore Excel people know that we can use the arrow keys to go back to that field.
Unfortunately, Power Pivot does not support the arrow keys you have to either start to type the field name or just use the mouse to click on it.
I know most hardcore people hate to use the mouse.
But here we are that's it's just that, what we have to do. Click the closing parentheses and very quickly it will give us the Year column for those 1.8 million rows.
Again, bad heading, I wish they would just prompt us for the heading because we're gonna have to change that heading every single time.
I'll call this Year.
All right and I could do similar things for a Month and for a Day using Month and Day Functions.
Again, most functions that we use here in this grid are exactly like the Functions that we use in Excel.
The one clearing difference is the Text Function, which is so commonly used in Excel is not spelled text here is spelled as Format.
So, we'll use equal Format, click on the field that we want, a comma and then in quotes whatever format we would like to use.
So, I'm going to spell it the month completely, that would be 4 M's in quotes, closing quotes, closing parentheses, press enter and let that calculate down.
And again once we get the answer, you have to right-click and change that horrible heading to something a bit more friendly.
All right so, there you have it.
Just a sampling of some of the many DAX Functions that you can use to create new calculations right here in the grid.
Chapter 6 has a complete list of all those DAX Functions and examples of how to use them.
I figure this is the one that you'll run into most often after it this is what we do in the podcast.
All right, well I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
