From the course: Learning Data Analytics: 1 Foundations

Combining data with merge columns

From the course: Learning Data Analytics: 1 Foundations

Combining data with merge columns

“

- [Instructor] Systems store data individually, mainly for sorting purposes. For example, if first name and last name were always contained together, we could never sort by last name and then by first name. To make our data sets more readable, we typically use functions like CONCAT, or concatenate. That allows us to combine the first and last name together. In Power Query, we do this by using merge column on the fields. One of the benefits of merging the fields is that we can remove the river of white space that flows through the lines. For example, if you look between B and C, what you'll notice is that there is a space gap. All of the names end in different places and the next name starts in the same place, and this causes our reports to be less readable. OK, let's go ahead and work with these fields by merging them together. All right, I'm going to right-click and go to my header and go to Edit. OK, I'm on my sales order header here, and I also see sales order header. OK, what I want to do is I want to combine my first and last name together. So I'm going to click on first name. I'll hold control and click on last name. I'll right-click and choose merge columns. I'll name this DisplayName. Now I do want to include a separator because I don't want to put them right up against each other. I want to have a space there. I'll do OK, and now my original name for first name and last name have been combined into one field. Now, if I want to sort, I'm in trouble. So let's do this, let's delete that. And then we get our first and our last name together. Let's go ahead and sort by last name, and then we'll sort by first name. OK, now that sorting step will occur before the merge step. All right, so now I'll hold my control. Click that last name and then merge those columns together again. I'll do DisplayName. I'll do a space, and I'll click OK. OK, now I have them sorted properly, and I have them merged. Let me show you another way to do this. Let's go to sales order lines. So now I want to keep my first name and my last name columns, but I also want to display it as last name, comma, space, first name, which is common. So I'll go ahead and right click on last name and choose duplicate. And I get last that name column. I'll go ahead, go back over, right-click and duplicate my first name. Notice the order, last name and then first name. And these are also copies. Meaning I'm going to merge these together to create a new display column, and I will still have my original last name and first name data. I'll go ahead and highlight last name. I'll highlight first name. I'll right-click and merge them. I'll call this DisplayName, and then I'll do a custom separator, which will be a comma and a space. And then I'll click OK. Now I want to move DisplayName to the front of the dataset. I'll right-click it, go to move, and move it to the beginning. OK, and then from here I can drag it over, past the customer key. Awesome, let's do close and load. This is a major time saver when you're constantly combining fields together. Remember, to keep your original columns or not is a preference or a requirement depending on your reporting needs. It's so easy to duplicate these fields so that you can actually have it all.

Contents