I have an excel table list with information like this:
| Old Consumer Id | Old Company Name |
| "H065324" | WATER SYSTEM |
| "H065324" | AT&T |
| "H070209" | WATER SYSTEM |
| "H070209" | TAX ASSESSOR-COLLEC |
| "H078806" | PNC VISA |
| "H078806" | BANK AMERICA MERRILL LYNCH VISA |
| "H078806" | CITIBANK |
| "H078806" | CITIBANK |
| "H115228" | T URESTI, MPA, PCC |
| "H135408" | T URESTI, MPA, PCC |
| "H10140606" | SAN ANTONIO WATER SYSTEM |
| "H10140606" | EVERBANK |
But I want to re-arrange like this:
| Old Consumer Id | Old Company Name | |||
| "H10065324" | WATER SYSTEM | AT&T | ||
| "H10070209" | WATER SYSTEM | TAX ASSESSOR-COLLEC | ||
| "H10078806" | PNC VISA | BANK AMERICA MERRILL LYNCH VISA | CITIBANK | CITIBANK |
| "H10115228" | T URESTI, MPA, PCC | |||
| "H10135408" | T URESTI, MPA, PCC | |||
| "H10140606" | WATER SYSTEM | EVERBANK |
How can I do that?
I used Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Old Consumer Id"}, {{"Data", each _, type table [Old Consumer Id=text, Old Company Name=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Old Company Name", "Index"}, {"Old Company Name", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "Old Company Name")
in
#"Pivoted Column"
