Joining and Splitting Cells with Multiple Lines

[Data_N00b]

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

[Data_N00b · Accepted solution]

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:

1. Add a custom colun and do a text split by a line break/line feed: =Text.Split([Column A],"#(lf)")
2. Repeat Step 1 for Column B.
3. 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"})
4. Expand the the "Custom Colum" (created in Step 3)
5. 
6. Create a Custom Column, and merge the contents "=Text.Combine({[Column A New], " ", [Column B New]})"

Final Results:

[Arul]

@Data_N00b ,

you need to add one more column to get the desired result,

1. In power query editor go to add column and click custom column and enter the below formula by changing the necessary column names,

[Column A] &" "& [Column B]

2. Desired result,

Thanks,

Arul

[Data_N00b]

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.

[Arul]

@Data_N00b ,

sorry I did not understand but the above formula should work for all the lines in two columns.

Thanks,

Arul