Learn how to clean and transform raw data for visualization using both Tableau and Excel's Power Query. This article walks through unpivoting life expectancy data and building a meaningful pivot table to identify trends and outliers across countries.
Key Insights
- Transforming life expectancy data using Tableau's data interpreter and pivot features makes large datasets more manageable and improves visualization accuracy.
- Excel's Power Query can replicate Tableau’s data transformation by unpivoting year-based columns into a structured format with dimensions and measures suitable for pivot tables.
- Noble Desktop demonstrates how to use pivot tables in Excel to calculate average life expectancy and identify top-performing countries, highlighting the importance of data cleaning before visualization.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
So, my question to you is, now that we did that, if you click on sheet, it's very different. This actually looks manageable. I see country name, I see years, and I see life expectancy.
Country name is a dimension, years is a dimension, and life expectancy is the measure. So, my question to you is, can you create the original bad visualization we showcased at the beginning of the class? That's the first step, and then see if you can create a better one. This is not necessarily like a full exercise, but it's the closest that will come to me assigning you something to do and see if you can recreate a visualization based on what you already know.
So, if I go to the folder, that pivot feature didn't happen in the Excel file. I'm going to go to the World Bank data. There it is.
This is the file that I'm using. I'm going to open this up. That's the information I dragged in.
It's still the same. So, I can put in information from 2020 to 2025, and then I'll go through the process of using the data interpreter and then doing the pivot. Are you able to extract the pivoted edited data from Tableau to Excel? That I am not aware of.
I don't know if we could do that. I think Tableau likes to keep things in Tableau. If you export or save information, it's usually in a Tableau file.
But don't quote me on that. I think it's not possible to extract it from Tableau. I could show you how you could do the same thing in Excel.
I actually did it this morning. It's not that difficult. So, for those of you who want to watch, I'll show you how I can pivot the information here.
You'll just use Power Query. So, let me save a copy of this file and demonstrate this. You might prefer actually to do that, because this information is probably spit out of some kind of database, and maybe that's the only way you get the information.
You can pivot it or unpivot it in something called Power Query, and this is free and available in Excel. So, I'll do this on video and you'll see. So, I'll go here.
I'm going to select these three rows. I'm going to delete them. These are my headers.
I don't need these columns, so I'm going to delete them. I just have the years here. This is the information I want to pivot.
So, I'm going to do this in Power Query. Let me save a copy of this file so I don't mess it up. I'm going to save this as Life Expectancy, and I'll call it Pivot.
This will be on the video, and so I want to go over to Data, and I'm going to choose From Table Arrange. I'm going to use Get and Transform. Some of you may already know how to do this.
It's selecting the data. I'm going to click OK. It opens up in Power Query.
Power Query has the information the same way it was set up in Tableau. Now, what I need to do is select all the years from 1960 to 2020, but actually I don't need 2020 because 2020 is null. So, I'm going to delete that column.
So, I'm going to go over here, and I'm going to remove this column. All right. So, I just need 2019 and up to 26 from 2020 to 1960.
I'll hold on to the Shift key and select. Okay. I'll do it again.
Select 1960. I'm going to hold on to the Shift key and click 2019. I've selected everything.
Okay. So, what do I do from here? I'm going to right-click, and I'm going to look for the option Unpivot Columns. Here, it's called Unpivot.
I have the same information set up exactly the same way I set it up in Tableau. Now, I can choose to load this information. I'm going to close and load it.
What happened to all my information? Oh, there it is. It took a little while, and I have it here in Excel. Now, this is easier for me to work with in a Pivot Table.
I have three columns, and the Pivot Table will automatically filter all the countries, and I can get the average life expectancy for all the years. So, I sort of knew that you could do this in Excel. I was just trying to do this morning, and I found the unpivot feature, and now this is information that Tableau would like.
Tableau would like you to do this and then save this as an Excel file, and then you would just drag and drop this into Tableau, and you'd have no problem. So, you can do this cleanup work in Excel, but if you don't know how to do it, Tableau says, well, I'm willing to help you with that. Let's say I do create a Pivot Table.
Let's go the extra mile. I'll click here. Click open.
So, I want to bring in country name, and I want to go to value. Right now, it's set to sum, which doesn't make sense. So, I'll click the drop down here and go to value field settings and choose average.
I'll click okay. I have the average life expectancy. I'm going to right click, change the number format, take out the decimals.
Oh my gosh, there are certain countries I would not want to be in looking at some of the life expectancy, and I was playing with this morning. One of the things you could do is you could click the drop down here, go to value filters, and look for the top 10. I'll click top 10, and I want the top 10 items based on the average value.
I'll click okay. These are the top 10 countries you want to live in to have the highest life expectancy. I'll also go over here and use more sort options, and I'll sort in descending order by average value.
I'll click okay, and then here's my top 10. San Marino, Cayman Islands. I guess that's why people go to the Cayman Islands.
Lichtenstein, Faroe Islands, Bermuda. Some of this information is skewed because there are some countries that don't have information for multiple years, so their average may be skewed, but it's sort of like this. I can understand Japan, Norway, you know, if we extended this probably would see like Finland or something like that.
All right, but that's Excel work. Let's go back to Tableau. So, did anyone create this visualization? If you want, you can unmute yourself, and then I'll follow your instructions, and then we'll see how you could create this.