Excel Forum

Merge multiple rows in to one

baxbax

Member

Members

Power Query

Power Pivot

Forum Posts: 105

Member Since:
July 11, 2016

Offline

October 23, 2019 - 8:24 pm

Hi,

I have a data extract file which contains some employee contact details. In the file there are multiple rows that relate to the same employee. The number of rows per employee varies. It can be just one or it can be many. The first 4 columns contain the same data for the employee regardless of how many rows there are. The remaining columns have different data on each line.

What I would like to do is combine the rows so that we have just one row per employee. The data for the additional rows would be added as additional columns.

I have added a file that shows the source data format and the required output. This is just example data. In reality there are many more columns in the source data.

Any help greatly appreciated as always.

Regards

Bax

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1807

Member Since:
November 8, 2013

Offline

October 23, 2019 - 11:06 pm

Hi Bax,

You can Group By the first 4 columns , without aggregation (All Rows)

This will produce a new column with tables. If you look in a table, it will be an extract of the original table with all entries matching the first 4 columns.

In the next step, extract only one column at a time from the tables column, combining the items with any separator you want. I used a line feed below:

let
Source = Excel.CurrentWorkbook(){[Name="Table_Source_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Mobile", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"applicantid", "First Name", "Surname", "DOB"}, {{"Grouped", each _, type table [applicantid=number, First Name=text, Surname=text, DOB=datetime, email=text, Mobile=anynonnull, Phone=text, Street=text, City=text, PostCode=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Mobile", each Text.Combine( List.RemoveItems(Table.SelectColumns([Grouped],"Mobile")[Mobile],{"NULL"}),"#(lf)")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "email", each Text.Combine( List.RemoveItems(Table.SelectColumns([Grouped],"email"),{"NULL"}),"#(lf)")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Phone", each Text.Combine( List.RemoveItems(Table.SelectColumns([Grouped],"Phone")[Phone],{"NULL"}),"#(lf)"))
in
#"Added Custom2"

I added 3 columns, you can add the other 3 needed. As you can see, I also removed the entries with "NULL"