Log In
Register
Home
Power Query
July 11, 2016
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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:
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"
1 Guest(s)
