Forum Discussion
Joining and Splitting Cells with Multiple Lines
Hello,
I have searched around and couldn't find a similar question.;It might be how I am phrasing my question...
I am trying to join data into a new column. The 2 columns have multiple lines within a cell. When I make a column with a formula, it takes the contents of the first column followed by the second column. I am wanting to take the first line of 1 column and join it with the first line in the 2nd column, then seperate the new column by each line.
Please see below for an example of what I am trying to achieve. I know how to split cells into multiple rows for each line with in a cell, but I am having trouble joining the Xth line of each cell in one column to the xth line from another column.
| Data - Current State: | ||
| Column A | Column B | |
| A3 - Line 1 A3 - Line 2 A3 - Line 3 | B3 - Line 1 B3 - Line 2 B3 - Line 3 | |
| Desired Results: | ||
| A3 - Line 1 B3 - Line 1 | ||
| A3 - Line 2 B3 - Line 2 | ||
| A3 - Line 3 B3 - Line 3 |
I got a solution from a coworker. The solution uses text splits by line feeds (Text.Split M Code), creating a table from columns (Table.FromColumns M code), expanding a column, and merging columns (using Text.Combine M code)
Below is the table that served as an example.
Steps for transforming the table:
- Add a custom colun and do a text split by a line break/line feed: =Text.Split([Column A],"#(lf)")
- Repeat Step 1 for Column B.
- Create another custom column, You will create a Table from the splitting of Columns A and B. This basically creates a table within a table. This column was called Custom.
- =Table.FromColumns({[Split Column A],[Split Column B]},{"Column A New", "Column B New"})
- Expand the the "Custom Colum" (created in Step 3)
- Create a Custom Column, and merge the contents "=Text.Combine({[Column A New], " ", [Column B New]})"
Final Results:
4 Replies
Hi Arul,
Thank you for helping me.
One of the problems I have is that there are multiple lines in the 2 columns. When I try the & symbol it just takes Colunm A contents and joins contents from Column B right after Column A's contents.
I got a solution from a coworker. The solution uses text splits by line feeds (Text.Split M Code), creating a table from columns (Table.FromColumns M code), expanding a column, and merging columns (using Text.Combine M code)
Below is the table that served as an example.
Steps for transforming the table:
- Add a custom colun and do a text split by a line break/line feed: =Text.Split([Column A],"#(lf)")
- Repeat Step 1 for Column B.
- Create another custom column, You will create a Table from the splitting of Columns A and B. This basically creates a table within a table. This column was called Custom.
- =Table.FromColumns({[Split Column A],[Split Column B]},{"Column A New", "Column B New"})
- Expand the the "Custom Colum" (created in Step 3)
- Create a Custom Column, and merge the contents "=Text.Combine({[Column A New], " ", [Column B New]})"
Final Results:
