From the course: Learning Data Analytics: 1 Foundations

Working with reusable data

From the course: Learning Data Analytics: 1 Foundations

Working with reusable data

“

- [Instructor] There are some datasets I consider standard and reusable, like a dates table. A dates table is one of the more common scenarios you'll run into with reusable data. It allows you to bring your dates in with common date calculations that you might find you're rebuilding every time you work with dates. Let's create our own custom dates table using Excel. That way you can use it anytime you have data-related calculations on dates. Okay, I've opened up the Excel dates file, and I want to go ahead and create just a few date calculations. Again, this file goes from 1/1/2019 to 12/31/2025. So I'll use equal and value to convert this to the serial number. That way, if I need serial number, I can use it. I'll go ahead and do day of week. So I'll do equal. I'll do weekday. Now it asks for serial number, but I could choose either A2 or B2, and it'll still work. And then I'll double click to auto-fill that down. By default, the first day of the week is Sunday. I can change that if I need to for my reporting purposes. Okay, the weekday name, like Monday, Tuesday, Wednesday. Now I know I can format the date to look that way, but I want the actual data, not just a date to work with. Okay, so I'll do equal, text, point to my date there, I'll type a comma, and I'll do four Ds. And this will give me the full name. So a full Tuesday, a full Wednesday. If I wanted the abbreviation, I would just do three Ds. Okay, go ahead and press enter there. Okay, so you see Saturday is seven. Sunday is one. Again, every organization may deal with that a little bit differently. All right, I want to pull just the month number. So I'll do equal month. I'll grab that date or that serial number. Either one will work. And auto-fill that. Okay, I want to pull the month name. Again, sometimes it's more flexible to have just the text version of the name, versus constantly formatting a date to look a certain way. All right, so I'll do equal, text. Choose my date. And, again, I've got a decision to make. Do I want the three-letter abbreviation? I would just end it with three Ms. If I want it fully spelled out, I'll do four. Okay, let's go ahead and do the year. End of the month can be valuable if you're doing invoicing, but you take orders throughout. Just depends on your business rules. Let's go ahead and include that here. So if there's something that I'm constantly trying to calculate, I would want to include it here. So I'll do equal EOMONTH. My start date is here, and I want the end of this particular month. So I'll go ahead and put a zero here, and then I'll go ahead and close that. And it will find the last day of the month for any day. So on the ninth, it still finds the 31st. Okay, so now I've got my dates table ready. All I need to do is save it and then I can import it into any data set that I need to work with these particular date codes, or I can link directly to it for any of my Power BI or Power Query needs. I'll go ahead and save that. There are other valuable reusable datasets like postal codes that have latitude and longitude. That way you can use it for mapping data. Geonames.org is one of the most popular sites for pulling free postal code data from all over the world. You'll be able to use your new data skills to extract and clean exactly what you need for your projects. You can create your datasets, or you can find amazing datasets to fit your needs. Just consider the data you use on a routine basis and create a process to maintain these reusable datasets to save you time.

Contents