Power Query: Row Together Part 1

Welcome to our Power Query blog.  This week, I look at how to combine rows of similar data.

Often when using Power Query to solve a problem, there is more than one way to attack it.  This is true when it comes to combining multiple rows to make one row.  The solution will depend upon the requirements of the data and how the rows are connected.  I will start with a simple scenario.

The task is to create a row for each Salesperson containing all the regions.  The end result will look like this:

I will begin by extracting the data to Power Query using 'Get Data from Table/Range'.  Note that my data is in a Table called RegionData.

I don't need the 'Changed Type' step, so I delete it using the cross next to the step name.

Now I have my query, I need to group the data for each Salesperson together.  I begin by merging Region 1 and Region 2.  I select both columns and right-click to view the 'Merge Columns' option:

I can use any separator as I will be dividing the column up later using the same separator.  I choose to use a comma.  I name the merged column 'Region':

I have all my region data in one column:

I need to group Region for each Salesperson.  I am going to use a technique I used in Power Query: Group Text.  I begin by using the 'Group By' functionality available in the Home tab:

There is no option to concatenate the text, so I choose to Sum. This of course gives me an error, but I have the basis of the syntax for this step:

I change the M code from this:

to use Text.Combine() instead, remembering to add the column separator:

This combines the Region data:

(Don't be concerned if the red bar under the column heading for Region is showing at this point as the error checking can lag sometimes.)

I now have the regions in one row for each Salesperson.  To get them into individual columns, I can use 'Split Column' on the Home tab:

I choose to 'Split Column' using the 'By Delimiter' option:

I choose to split at every comma:

I need to change the data type of the Salesperson column to Text, and then I 'Close & Load To…' on the Home tab:

I begin by using the 'Only Create Connection' option.  I intend to put the data on an existing worksheet.  I can then right-click on my query to access the ‘Import Data’ dialog again by choosing 'Load To…' .

I choose to put the results on the Outputs worksheet:

I have solved this particular task, and next time I will change the inputs and consider how I can make the solution more flexible.

Come back next time for more ways to use Power Query!