SOLVED

How to consolidate repeated records on a table

Frequent Contributor

I have 2 tables, I would like to convert, left to right in this example. Only way I have done it is by using a lot of steps on Power Query which slows down my computer and often crashed (original file is many thousands records)


These are 2 tables, Column A and B, Column F and G. I want to convert left table to right table. If there is any way to do the other way that would be great too.

 

 

ABCDEFG
Table1[ID]Table1[Location]   Table2[ID]Table2[Location]
X0001Place 1   X0001Place 1 + Place 9
X0001Place 9   X0002Place 1 + Place 9
X0002Place 1   X0003Place 1 + Place 9
X0002Place 9   X0004Place 1 + Place 5 + Place 9
X0003Place 1   X0005Place 5 + Place 9
X0003Place 9   X0006Place 7  +  Place 9
X0004Place 1   X0007Place 6
X0004Place 5   X0008Place 6
X0004Place 9   X0009Place 5 + Place 9
X0005Place 5   X0010Place 5 + Place 9
X0005Place 9   X0011Place 5 + Place 9
X0006Place 7   X0012Place 5 + Place 9
X0006Place 9   X0013Place 5
X0007Place 6   X0014Place 5 + Place 8 + Place 9
X0008Place 6   X0015Place 5 + Place 9
X0009Place 5   X0016Place 5 + Place 9
X0009Place 9   X0017Place 5 + Place 9
X0010Place 5   X0018Place 5
X0010Place 9   X0019Place 5
X0011Place 5   X0020Place 5
X0011Place 9   X0021Place 3
X0012Place 5   X0022Place 5 + Place 8 + Place 9
X0012Place 9   X0023Place 3
X0013Place 5   X0024Place 2 + Place 5
X0014Place 5   X0025Place 3
X0014Place 8   X0026Place 3
X0014Place 9   X0027Place 3
X0015Place 5   X0028Place 5
X0015Place 9   X0029Place 3
X0016Place 5   X0030Place 3
X0016Place 9   X0031Place 2 + Place 5
X0017Place 5   X0032Place 2 + Place 5
X0017Place 9   X0033Place 2 + Place 5
X0018Place 5   X0034Place 3
X0019Place 5   X0035Place 3
X0020Place 5   X0036Place 5 + Place 9
X0021Place 3   X0037Place 4
X0022Place 5   X0038Place 3
X0022Place 8   X0039Place 3
X0022Place 9   X0040Place 3
X0023Place 3   X0041Place 3
X0024Place 2   X0042Place 5 + Place 7 + Place 8
X0024Place 5   X0043Place 3
X0025Place 3   X0044Place 3
X0026Place 3   X0045Place 3
X0027Place 3   X0046Place 3
X0028Place 5   X0047Place 3
X0029Place 3   X0048Place 3
X0030Place 3   X0049Place 3
X0031Place 2   X0050Place 5
X0031Place 5   X0051Place 5
X0032Place 2   X0052Place 5 + Place 8
X0032Place 5   X0053Place 3
X0033Place 2   X0054Place 8  +  Place 9
X0033Place 5   X0055Place 2 + Place 5
X0034Place 3   X0056Place 2 + Place 5
X0035Place 3   X0057Place 5
X0036Place 5   X0058Place 5
X0036Place 9   X0059Place 5
X0037Place 4   X0060Place 3 + Place 5
X0038Place 3   X0061Place 5
X0039Place 3   X0062Place 5
X0040Place 3   X0063Place 2 + Place 5
X0041Place 3   X0064Place 5
X0042Place 5   X0065Place 2
X0042Place 7   X0066Place 3
X0042Place 8   X0067Place 3 + Place 8
X0043Place 3   X0068Place 9
X0044Place 3   X0069Place 8  +  Place 9
X0045Place 3   X0070Place 8
X0046Place 3   X0071Place 8
X0047Place 3   X0072Place 8
X0048Place 3   X0073Place 4
X0049Place 3   X0074Place 5
X0050Place 5   X0075Place 8
X0051Place 5   X0076Place 5
X0052Place 5   X0077Place 5
X0052Place 8   X0078Place 5
X0053Place 3   X0079Place 3
X0054Place 8   X0080Place 8
X0054Place 9   X0081Place 3
X0055Place 2   X0082Place 3
X0055Place 5   X0083Place 3
X0056Place 2   X0084Place 3
X0056Place 5   X0085Place 5 + Place 9
X0057Place 5   X0086Place 3
X0058Place 5   X0087Place 8
X0059Place 5   X0088Place 8
X0060Place 3   X0089Place 5
X0060Place 5   X0090Place 8
X0061Place 5   X0091Place 5
X0062Place 5   X0092Place 8
X0063Place 2   X0093Place 5
X0063Place 5   X0094Place 5
X0064Place 5   X0095Place 5
X0065Place 2   X0096Place 5
X0066Place 3   X0097Place 5
X0067Place 3   X0098Place 4 + Place 8
X0067Place 8   X0099Place 4
X0068Place 9   X0100Place 8
X0069Place 8   X0101Place 2 + Place 5
X0069Place 9   X0102Place 5
X0070Place 8   X0103Place 3
X0071Place 8   X0104Place 3
X0072Place 8   X0105Place 8
X0073Place 4   X0106Place 4
X0074Place 5   X0107Place 4
X0075Place 8   X0108Place 5
X0076Place 5   X0109Place 4
X0077Place 5   X0110Place 3 + Place 4
X0078Place 5   X0111Place 3 + Place 4
X0079Place 3   X0112Place 4
X0080Place 8   X0113Place 4
X0081Place 3   X0114Place 3 + Place 4
X0082Place 3   X0115Place 4
X0083Place 3     
X0084Place 3     
X0085Place 5     
X0085Place 9     
X0086Place 3     
X0087Place 8     
X0088Place 8     
X0089Place 5     
X0090Place 8     
X0091Place 5     
X0092Place 8     
X0093Place 5     
X0094Place 5     
X0095Place 5     
X0096Place 5     
X0097Place 5     
X0098Place 4     
X0098Place 8     
X0099Place 4     
X0100Place 8     
X0101Place 2     
X0101Place 5     
X0102Place 5     
X0103Place 3     
X0104Place 3     
X0105Place 8     
X0106Place 4     
X0107Place 4     
X0108Place 5     
X0109Place 4     
X0110Place 3     
X0110Place 4     
X0111Place 3     
X0111Place 4     
X0112Place 4     
X0113Place 4     
X0114Place 3     
X0114Place 4     
X0115Place 4     
3 Replies
Hello,

Kindly make me understand what you me by "I want to convert left table to right table"

I believe Flash Fill "will" do what you wanna achieve... Just need for more explanation based on the sample provided

@Abiola1 

 

These are 2 tables, one is column A and B, the other is column D and E. I want to convert table 1 to table 2

best response confirmed by Ocasio27 (Frequent Contributor)
Solution

@Ocasio27 

The easiest way is with Power Query - query the source, group by ID using function as here for aggregation

let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    #"Grouped Rows" = Table.Group(Source,
        {"ID"},
        {
            {"Locations", each Text.Combine(_[Location]," + ")}
        }
    )
in
    #"Grouped Rows"

Result is like

image.png