Return to Answer

As mentioned, in Excel 2016 you should have access to the Power Query Editor through Data tab -> From Table/Range

Then combining the text as you have done and loading to a new table should be easy - this is the entire code:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Concat = Table.Group(Source, {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}})

The first line loads "Table1" from your workbook. The second line Groups the table rows based on {"WO"} column, and aggregates each group of rows using the Text.Combine function on the [TEXT] column of the table, with spaces as separators.

To generate that you follow these steps:

• Click inside the table with the data
• Data Tab -> From Table/Range to launch PowerQuery (PQ)
• Home -> GroupBy
• Choose the WO column in the first dropdown, and for the aggregation function choose anything, we'll overwrite this
• Click okay and now in the formula bar you should see = Table.Group(#"Changed Type", {"WO"} ..., replace that with = Table.Group(#"Changed Type", {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}}). Hit enter
  • Optional - In the steps pane on the right you can right click delete the Changed Type step as it is not really needed. You can also rename the steps.
• Now in the top left hit the close and load 💾 icon and your PQ will load to a new tab by default. Every time your source data changes, go Data -> Refresh All to re-run the PQ.
  • Optional - You can use the dropdown under the save icon to Close and Load To a different location, e.g. a table next to the source data

final result

Justification

Like VBA

• It is built into Excel and the powerquery travels embedded in your workbook so you won't lose it
• It can dynamically switch to pulling data from different sources

This is an improvement over VBA because

• It is simpler (much less code) and easier to understand, adjust and modify as a result
• It should be very fast, powerquery can run multithreaded and generally is optimised for data manipulation
• You will find it supports built in features VBA does not, if you want to build on this data manipulation in future
• The editor for powerquery is graphical - so you don't type the code but instead click buttons to insert it for you, making it simpler to learn and less error prone

As mentioned, in Excel 2016 you should have access to the Power Query Editor through Data tab -> From Table/Range

Then combining the text as you have done and loading to a new table should be easy - this is the entire code:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Concat = Table.Group(Source, {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}})

The first line loads "Table1" from your workbook. The second line Groups the table rows based on {"WO"} column, and aggregates each group of rows using the Text.Combine function on the [TEXT] column of the table, with spaces as separators.

To generate that you follow these steps:

• Click inside the table with the data
• Data Tab -> From Table/Range to launch PowerQuery (PQ)
• Home -> GroupBy
• Choose the WO column in the first dropdown, and for the aggregation function choose anything, we'll overwrite this
• Click okay and now in the formula bar you should see = Table.Group(#"Changed Type", {"WO"} ..., replace that with = Table.Group(#"Changed Type", {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}}). Hit enter
  • Optional - In the steps pane on the right you can right click delete the Changed Type step as it is not really needed. You can also rename the steps.
• Now in the top left hit the close and load 💾 icon and your PQ will load to a new tab by default. Every time your source data changes, go Data -> Refresh All to re-run the PQ.
  • Optional - You can use the dropdown under the save icon to Close and Load To a different location, e.g. a table next to the source data

final result

Justification

Like VBA

• It is built into Excel and the powerquery travels embedded in your workbook so you won't lose it
• It can dynamically switch to pulling data from different sources

This is an improvement over VBA because

• It is simpler (much less code) and easier to understand, adjust and modify as a result
• It should be very fast, powerquery can run multithreaded and generally is optimised for data manipulation
• You will find it supports built in features VBA does not, if you want to build on this data manipulation in future
• The editor for powerquery is graphical - so you don't type the code but instead click buttons to insert it for you, making it simpler to learn and less error prone

Update

To join with newlines use Text.Combine([TEXT],"#(cr)#(lf)") instead of Text.Combine([TEXT]," "), or use Lines.ToText([TEXT]) but this adds a trailing newline.

As mentioned, in Excel 2016 you should have access to the Power Query Editor through Data tab -> From Table/Range

Then combining the text as you have done and loading to a new table should be easy - this is the code:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Concat = Table.Group(Source, {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}})

The first line loads "Table1" from your workbook. The second line Groups the table rows based on {"WO"} column, and aggregates each group of rows using the Text.Combine function on the [TEXT] column of the table, with spaces as separators.

To generate that you follow these steps:

• Click inside the table with the data
• Data Tab -> From Table/Range to launch PowerQuery (PQ)
• Home -> GroupBy
• Choose the WO column in the first dropdown, and for the aggregation function choose anything, we'll overwrite this
• Click okay and now in the formula bar you should see = Table.Group(#"Changed Type", {"WO"} ..., replace that with = Table.Group(#"Changed Type", {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}}). Hit enter
  • Optional - In the steps pane on the right you can right click delete the Changed Type step as it is not really needed. You can also rename the steps.
• Now in the top left hit the close and load 💾 icon and your PQ will load to a new tab by default. Every time your source data changes, go Data -> Refresh All to re-run the PQ.
  • Optional - You can use the dropdown under the save icon to Close and Load To a different location, e.g. a table next to the source data

final result

Justification

Like VBA

• It is built into Excel and the powerquery travels embedded in your workbook so you won't lose it
• It can dynamically switch to pulling data from different sources

This is an improvement over VBA because

• It is simpler (much less code) and easier to understand, adjust and modify as a result
• It should be very fast, powerquery can run multithreaded and generally is optimised for data manipulation
• You will find it supports built in features VBA does not, if you want to build on this data manipulation in future
• The editor for powerquery is graphical - so you don't type the code but instead click buttons to insert it for you, making it simpler to learn and less error prone

As mentioned, in Excel 2016 you should have access to the Power Query Editor through Data tab -> From Table/Range

Then combining the text as you have done and loading to a new table should be easy - this is the entire code:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Concat = Table.Group(Source, {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}})

The first line loads "Table1" from your workbook. The second line Groups the table rows based on {"WO"} column, and aggregates each group of rows using the Text.Combine function on the [TEXT] column of the table, with spaces as separators.

To generate that you follow these steps:

• Click inside the table with the data
• Data Tab -> From Table/Range to launch PowerQuery (PQ)
• Home -> GroupBy
• Choose the WO column in the first dropdown, and for the aggregation function choose anything, we'll overwrite this
• Click okay and now in the formula bar you should see = Table.Group(#"Changed Type", {"WO"} ..., replace that with = Table.Group(#"Changed Type", {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}}). Hit enter
  • Optional - In the steps pane on the right you can right click delete the Changed Type step as it is not really needed. You can also rename the steps.
• Now in the top left hit the close and load 💾 icon and your PQ will load to a new tab by default. Every time your source data changes, go Data -> Refresh All to re-run the PQ.
  • Optional - You can use the dropdown under the save icon to Close and Load To a different location, e.g. a table next to the source data

final result

Justification

Like VBA

• It is built into Excel and the powerquery travels embedded in your workbook so you won't lose it
• It can dynamically switch to pulling data from different sources

This is an improvement over VBA because

• It is simpler (much less code) and easier to understand, adjust and modify as a result
• It should be very fast, powerquery can run multithreaded and generally is optimised for data manipulation
• You will find it supports built in features VBA does not, if you want to build on this data manipulation in future
• The editor for powerquery is graphical - so you don't type the code but instead click buttons to insert it for you, making it simpler to learn and less error prone

As mentioned, in Excel 2016 you should have access to the Power Query Editor through Data tab -> From Table/Range

Then combining the text as you have done and loading to a new table should be easy - this is the code:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Concat = Table.Group(Source, {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}})

The first line loads "Table1" from your workbook. The second line Groups the table rows based on {"WO"} column, and aggregates each group of rows using the Text.Combine function on the [TEXT] column of the table, with spaces as separators.

To generate that you follow these steps:

• Click inside the table with the data
• Data Tab -> From Table/Range to launch PowerQuery (PQ)
• Home -> GroupBy
• Choose the WO column in the first dropdown, and for the aggregation function choose anything, we'll overwrite this
• Click okay and now in the formula bar you should see = Table.Group(#"Changed Type", {"WO"} ..., replace that with = Table.Group(#"Changed Type", {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}}). Hit enter
  • Optional - In the steps pane on the right you can right click delete the Changed Type step as it is not really needed. You can also rename the steps.
• Now in the top left hit the close and load 💾 icon and your PQ will load to a new tab by default. Every time your source data changes, go Data -> Refresh All to re-run the PQ.
  • Optional - You can use the dropdown under the save icon to Close and Load To a different location, e.g. a table next to the source data

final result

Justification

Like VBA

• It is built into Excel and the powerquery travels embedded in your workbook so you won't lose it
• It can dynamically switch to pulling data from different sources

This is an improvement over VBA because

• It is simpler (much less code) and easier to adjust and modify as a result
• It should be very fast, powerquery can run multithreaded and generally is optimised for data manipulation
• You will find it supports built in features VBA does not, if you want to build on this data manipulation in future
• The editor for powerquery is graphical - so you don't type the code but instead click buttons to insert it for you, making it simpler to learn and less error prone

As mentioned, in Excel 2016 you should have access to the Power Query Editor through Data tab -> From Table/Range

Then combining the text as you have done and loading to a new table should be easy - this is the code:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Concat = Table.Group(Source, {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}})

The first line loads "Table1" from your workbook. The second line Groups the table rows based on {"WO"} column, and aggregates each group of rows using the Text.Combine function on the [TEXT] column of the table, with spaces as separators.

To generate that you follow these steps:

• Click inside the table with the data
• Data Tab -> From Table/Range to launch PowerQuery (PQ)
• Home -> GroupBy
• Choose the WO column in the first dropdown, and for the aggregation function choose anything, we'll overwrite this
• Click okay and now in the formula bar you should see = Table.Group(#"Changed Type", {"WO"} ..., replace that with = Table.Group(#"Changed Type", {"WO"}, {{"TEXT", each Text.Combine([TEXT]," "), type text}}). Hit enter
  • Optional - In the steps pane on the right you can right click delete the Changed Type step as it is not really needed. You can also rename the steps.
• Now in the top left hit the close and load 💾 icon and your PQ will load to a new tab by default. Every time your source data changes, go Data -> Refresh All to re-run the PQ.
  • Optional - You can use the dropdown under the save icon to Close and Load To a different location, e.g. a table next to the source data

final result

Justification

Like VBA

• It is built into Excel and the powerquery travels embedded in your workbook so you won't lose it
• It can dynamically switch to pulling data from different sources

This is an improvement over VBA because

• It is simpler (much less code) and easier to understand, adjust and modify as a result
• It should be very fast, powerquery can run multithreaded and generally is optimised for data manipulation
• You will find it supports built in features VBA does not, if you want to build on this data manipulation in future
• The editor for powerquery is graphical - so you don't type the code but instead click buttons to insert it for you, making it simpler to learn and less error prone