From the course: Excel: Power Query (Get & Transform) (2020)

Data types explained

From the course: Excel: Power Query (Get & Transform) (2020)

Start my 1-month free trial

Data types explained

“

- [Instructor] Datatypes in Power Query. We got to talk about them. They are very strict and they won't let you do weird stuff like this. Watch, I am about to go equals, and I'm going to take the child fee, add it to a birthday, and then add an ID, hit enter, there's a number. I should not be able to do math on those types of datatypes. So I'm going to get rid of this malarkey, delete, and then we're going to to in to Power Query, talk about datatypes. Here we go. Data, from table/range, OK. First of all notice over in applied steps, there is a changed type step. Power Query automatically does that, and it's really a guess about the type of data that it sees in each column. Now let's go down the columns. In the name column, A, B, C. That's showing that we have a text column. ID, that one, two, three means we have whole numbers. And then we go to birthdate. It's put times in. That calendar with the clock? That means date and time. We can change that, because Power Query guessed wrong. Highlight there, and then change that to date. Yes, replace that guessed step. Then in the children's column, we got A, B, C, one, two, three, showing that there is text and numbers in this column. See, the none instead of a zero. And then the no. Notice that no is something Power Query put in place where there was a blank cell in our source data. It didn't leave it blank, and it didn't put a zero in. But what we can do, because we know that this should be whole numbers in this column. Change that and then notice. Anything that was text is now showing up as an error, because it is not a whole number. Let's try to add those values that I did in Excel. I'm going to go to add column, custom column, add child fee, plus birthday, plus ID, OK. We've got errors in that column. Click on one of those cells. What is the error? We can not apply operator plus to types number and date. It prevents it. So let's get rid of this column. Let's try one other thing. We want to add the name, then a colon space, and then the ID. Add a custom column. Go here, and then select name, ampersand, double quotes, comma, space, double quote, ampersand, ID, OK. What is the error? We cannot use an ampersand or a concatenation to types text and number; they have to be the same thing. All right, so I'm going to slide over here, and then I'm going to change this ID to text, okay? And a lot of people are of the mind that if you do have numbers that you're never going to do math on, go ahead and change them to text for this type of reason. Like a credit card number. You're never going to do any math with that. Let's move this change type step above the added custom. Let's click on added custom. Now, there is the column that we wanted, because now we're putting text with text. Let's get rid of this. Let's deal with the children column, because we want to multiply children times child fee. Let's highlight this column, right click it, replace errors, replace errors with what, zero, OK. And then let's replace that null. Right click it, replace values. Replace null with zero, OK. Then we can go ahead and multiply those columns. With that one highlighted, hold down the control key. Highlight child fee. We're at add column. Go to standard, multiply. There we are; we have the total child fee. And these are some of the things that you have to watch out for when you're working with datatypes in Power Query.

Contents