From the course: Learning Data Analytics: 1 Foundations

Convert data in Power Query

From the course: Learning Data Analytics: 1 Foundations

Convert data in Power Query

“

- [Instructor] Excel's been around for a while, and most people who are ever exposed to data find themselves in an Excel file. That means that they've learned to clean data, trim data and convert data with functions, and maybe even automate it with macros. This makes a lot of sense until the advent of Power Query. Power Query makes cleaning and converting data so much simpler. If you have a data set, you can easily transform it just by grabbing it from the sheet. That means any data, connected or flat, can be cleaned here using these techniques. Let's take a look at our order data. I'll go to the data tab, and in the get and transform data section, I'll choose from table range. It identifies my data and asks me if I have headers, which I do, so I'll go ahead and click okay. Now, it's important to note that when I bring this data in, it automatically has a couple of steps. So you see source and change type, okay, I'll click on the source step. And I want you to look at the data types. You can clearly see them displayed by the column name, and when I click change type, I see that it converted my customer key too. Power Query uses the first real row of data, not the headers, to chance to what it believes is the appropriate data type. It doesn't always give us what we want though, but it is a pretty fantastic step overall. If you spend a lot of time converting or formatting data, then this is immediately a productivity boost. If you really want to control the data typing, you can actually delete that step, you just hit that X there. We want to keep for the most part the changes that it made, but the customer key is currently set to a whole number, and that means if we put it into a pivot table, for example, it'll read that whole number and default to sum. Because I'll use it a lot for counting, I may want to go ahead and convert it back to text so it'll count by default. I'll click that one, two, three, and I'll just choose text. It's asking me, do I want to replace this change type step? I don't, I want to add a new step. I'll go ahead and scroll over and take a look, I'll go ahead and address my product key because I imagine I'll count it a lot. I'll make that text. I see my date order and my date shipped, I really only need dates here, so I'll go ahead and convert those by clicking on that little date icon and making them just date. Go ahead and scroll over a little bit further, so it's made my unit price a decimal. So I'll go ahead and make that currency, order quantity, it's fine, my discount amount really should be currency. I'll go ahead and adjust my tax amount and my sales amount, and then my total order. Okay. I think I'm good to go, I'll go ahead and choose close and load. I have my original data, which is my order data, and I have my new clean data. I'll go ahead and name this sheet cleaned. I'll note that I have 60,351 rows loaded. When I hover over that, it gives me that little peak of information. Power Query offers a ton of productivity for data routines that people do all the time, whether they're a data analyst or not. You've had the ability to use it for years, but if you're just now discovering it, don't worry, it's only getting better.

Contents