New column name: Enter a name column of combined cells (I’ve used Equipment).
Select all the columns, except the column to be combined, in our example, we need to select the First Name and Last Name columns. But don’t worry, because I’ll explain why it occurs and what we do about it. In our next transformation, we will create an error. The Equipment and Equipment Attribute columns have now been combined into one, as shown by the screenshot below. I’ve given the new column the name Equipment then we can click OK. So, we need to select –Custom– from the Separator drop-down list, and in the box below enter Colon ( : ), followed by a space character. Select the Equipment and Equipment Attribute columns, then click Transform -> Merge Columnsįor our example, we want to place a colon and space between the two column values. Now we’re ready to start the transformations.įirst, we will combine Equipment and Equipment Attribute into a single column, then secondly, combine the rows into a single cell for each individual. The Power Query window will open and show the data from the table. Select a cell within the data table, then click Data -> From Table / Range But with Power Query, it becomes a few simple transformations. This all sounds simple, doesn’t it? Before Power Query, we would need to use complex formulas to achieve this result. The goal is to combine all the equipment information for each employee into a single cell (as shown below). We can see that Anna Watkins has a laptop, access card, phone, and home printer. In the screenshot above, we have a list of employees, along with any equipment the company has allocated to them. We will be using this throughout the post.