Aug 07 2020 01:36 PM - edited Aug 07 2020 01:54 PM
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.
A | B | C | D | E | F | G |
Table1[ID] | Table1[Location] | Table2[ID] | Table2[Location] | |||
X0001 | Place 1 | X0001 | Place 1 + Place 9 | |||
X0001 | Place 9 | X0002 | Place 1 + Place 9 | |||
X0002 | Place 1 | X0003 | Place 1 + Place 9 | |||
X0002 | Place 9 | X0004 | Place 1 + Place 5 + Place 9 | |||
X0003 | Place 1 | X0005 | Place 5 + Place 9 | |||
X0003 | Place 9 | X0006 | Place 7 + Place 9 | |||
X0004 | Place 1 | X0007 | Place 6 | |||
X0004 | Place 5 | X0008 | Place 6 | |||
X0004 | Place 9 | X0009 | Place 5 + Place 9 | |||
X0005 | Place 5 | X0010 | Place 5 + Place 9 | |||
X0005 | Place 9 | X0011 | Place 5 + Place 9 | |||
X0006 | Place 7 | X0012 | Place 5 + Place 9 | |||
X0006 | Place 9 | X0013 | Place 5 | |||
X0007 | Place 6 | X0014 | Place 5 + Place 8 + Place 9 | |||
X0008 | Place 6 | X0015 | Place 5 + Place 9 | |||
X0009 | Place 5 | X0016 | Place 5 + Place 9 | |||
X0009 | Place 9 | X0017 | Place 5 + Place 9 | |||
X0010 | Place 5 | X0018 | Place 5 | |||
X0010 | Place 9 | X0019 | Place 5 | |||
X0011 | Place 5 | X0020 | Place 5 | |||
X0011 | Place 9 | X0021 | Place 3 | |||
X0012 | Place 5 | X0022 | Place 5 + Place 8 + Place 9 | |||
X0012 | Place 9 | X0023 | Place 3 | |||
X0013 | Place 5 | X0024 | Place 2 + Place 5 | |||
X0014 | Place 5 | X0025 | Place 3 | |||
X0014 | Place 8 | X0026 | Place 3 | |||
X0014 | Place 9 | X0027 | Place 3 | |||
X0015 | Place 5 | X0028 | Place 5 | |||
X0015 | Place 9 | X0029 | Place 3 | |||
X0016 | Place 5 | X0030 | Place 3 | |||
X0016 | Place 9 | X0031 | Place 2 + Place 5 | |||
X0017 | Place 5 | X0032 | Place 2 + Place 5 | |||
X0017 | Place 9 | X0033 | Place 2 + Place 5 | |||
X0018 | Place 5 | X0034 | Place 3 | |||
X0019 | Place 5 | X0035 | Place 3 | |||
X0020 | Place 5 | X0036 | Place 5 + Place 9 | |||
X0021 | Place 3 | X0037 | Place 4 | |||
X0022 | Place 5 | X0038 | Place 3 | |||
X0022 | Place 8 | X0039 | Place 3 | |||
X0022 | Place 9 | X0040 | Place 3 | |||
X0023 | Place 3 | X0041 | Place 3 | |||
X0024 | Place 2 | X0042 | Place 5 + Place 7 + Place 8 | |||
X0024 | Place 5 | X0043 | Place 3 | |||
X0025 | Place 3 | X0044 | Place 3 | |||
X0026 | Place 3 | X0045 | Place 3 | |||
X0027 | Place 3 | X0046 | Place 3 | |||
X0028 | Place 5 | X0047 | Place 3 | |||
X0029 | Place 3 | X0048 | Place 3 | |||
X0030 | Place 3 | X0049 | Place 3 | |||
X0031 | Place 2 | X0050 | Place 5 | |||
X0031 | Place 5 | X0051 | Place 5 | |||
X0032 | Place 2 | X0052 | Place 5 + Place 8 | |||
X0032 | Place 5 | X0053 | Place 3 | |||
X0033 | Place 2 | X0054 | Place 8 + Place 9 | |||
X0033 | Place 5 | X0055 | Place 2 + Place 5 | |||
X0034 | Place 3 | X0056 | Place 2 + Place 5 | |||
X0035 | Place 3 | X0057 | Place 5 | |||
X0036 | Place 5 | X0058 | Place 5 | |||
X0036 | Place 9 | X0059 | Place 5 | |||
X0037 | Place 4 | X0060 | Place 3 + Place 5 | |||
X0038 | Place 3 | X0061 | Place 5 | |||
X0039 | Place 3 | X0062 | Place 5 | |||
X0040 | Place 3 | X0063 | Place 2 + Place 5 | |||
X0041 | Place 3 | X0064 | Place 5 | |||
X0042 | Place 5 | X0065 | Place 2 | |||
X0042 | Place 7 | X0066 | Place 3 | |||
X0042 | Place 8 | X0067 | Place 3 + Place 8 | |||
X0043 | Place 3 | X0068 | Place 9 | |||
X0044 | Place 3 | X0069 | Place 8 + Place 9 | |||
X0045 | Place 3 | X0070 | Place 8 | |||
X0046 | Place 3 | X0071 | Place 8 | |||
X0047 | Place 3 | X0072 | Place 8 | |||
X0048 | Place 3 | X0073 | Place 4 | |||
X0049 | Place 3 | X0074 | Place 5 | |||
X0050 | Place 5 | X0075 | Place 8 | |||
X0051 | Place 5 | X0076 | Place 5 | |||
X0052 | Place 5 | X0077 | Place 5 | |||
X0052 | Place 8 | X0078 | Place 5 | |||
X0053 | Place 3 | X0079 | Place 3 | |||
X0054 | Place 8 | X0080 | Place 8 | |||
X0054 | Place 9 | X0081 | Place 3 | |||
X0055 | Place 2 | X0082 | Place 3 | |||
X0055 | Place 5 | X0083 | Place 3 | |||
X0056 | Place 2 | X0084 | Place 3 | |||
X0056 | Place 5 | X0085 | Place 5 + Place 9 | |||
X0057 | Place 5 | X0086 | Place 3 | |||
X0058 | Place 5 | X0087 | Place 8 | |||
X0059 | Place 5 | X0088 | Place 8 | |||
X0060 | Place 3 | X0089 | Place 5 | |||
X0060 | Place 5 | X0090 | Place 8 | |||
X0061 | Place 5 | X0091 | Place 5 | |||
X0062 | Place 5 | X0092 | Place 8 | |||
X0063 | Place 2 | X0093 | Place 5 | |||
X0063 | Place 5 | X0094 | Place 5 | |||
X0064 | Place 5 | X0095 | Place 5 | |||
X0065 | Place 2 | X0096 | Place 5 | |||
X0066 | Place 3 | X0097 | Place 5 | |||
X0067 | Place 3 | X0098 | Place 4 + Place 8 | |||
X0067 | Place 8 | X0099 | Place 4 | |||
X0068 | Place 9 | X0100 | Place 8 | |||
X0069 | Place 8 | X0101 | Place 2 + Place 5 | |||
X0069 | Place 9 | X0102 | Place 5 | |||
X0070 | Place 8 | X0103 | Place 3 | |||
X0071 | Place 8 | X0104 | Place 3 | |||
X0072 | Place 8 | X0105 | Place 8 | |||
X0073 | Place 4 | X0106 | Place 4 | |||
X0074 | Place 5 | X0107 | Place 4 | |||
X0075 | Place 8 | X0108 | Place 5 | |||
X0076 | Place 5 | X0109 | Place 4 | |||
X0077 | Place 5 | X0110 | Place 3 + Place 4 | |||
X0078 | Place 5 | X0111 | Place 3 + Place 4 | |||
X0079 | Place 3 | X0112 | Place 4 | |||
X0080 | Place 8 | X0113 | Place 4 | |||
X0081 | Place 3 | X0114 | Place 3 + Place 4 | |||
X0082 | Place 3 | X0115 | Place 4 | |||
X0083 | Place 3 | |||||
X0084 | Place 3 | |||||
X0085 | Place 5 | |||||
X0085 | Place 9 | |||||
X0086 | Place 3 | |||||
X0087 | Place 8 | |||||
X0088 | Place 8 | |||||
X0089 | Place 5 | |||||
X0090 | Place 8 | |||||
X0091 | Place 5 | |||||
X0092 | Place 8 | |||||
X0093 | Place 5 | |||||
X0094 | Place 5 | |||||
X0095 | Place 5 | |||||
X0096 | Place 5 | |||||
X0097 | Place 5 | |||||
X0098 | Place 4 | |||||
X0098 | Place 8 | |||||
X0099 | Place 4 | |||||
X0100 | Place 8 | |||||
X0101 | Place 2 | |||||
X0101 | Place 5 | |||||
X0102 | Place 5 | |||||
X0103 | Place 3 | |||||
X0104 | Place 3 | |||||
X0105 | Place 8 | |||||
X0106 | Place 4 | |||||
X0107 | Place 4 | |||||
X0108 | Place 5 | |||||
X0109 | Place 4 | |||||
X0110 | Place 3 | |||||
X0110 | Place 4 | |||||
X0111 | Place 3 | |||||
X0111 | Place 4 | |||||
X0112 | Place 4 | |||||
X0113 | Place 4 | |||||
X0114 | Place 3 | |||||
X0114 | Place 4 | |||||
X0115 | Place 4 |
Aug 07 2020 01:44 PM
Aug 07 2020 01:49 PM
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
Aug 10 2020 09:51 AM
SolutionThe 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
Aug 10 2020 09:51 AM
SolutionThe 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