Excel Column Splitting

[Excel_Learner]

Hi,

I want to split data available in 2 columns. It seems to be not straightforward, as I want multiple entry text data in a single cell (Column 1) to be mapped against same row Column 2 data, so that parallel data in consecutive cells gets mapped against each other.

E.g.:

Column 1	Column 2
1. Sam   2. John	Engineer Architect

I want final result as:

Column 1	Column 2
Sam	Engineer
John	Architect

Can someone help urgently?

[Vijay_A_Verma]

Use this query as your next step. Replace Source with your previou step name.

= Table.FromRecords(List.Transform(Table.ToRecords(Source), (x)=> Record.TransformFields(x, {"Column1", each Text.Trim(Text.AfterDelimiter(x[Column1], ".", 0))})))

[Excel_Learner]

Hi Vijay - not clear. These are my column names "Contact name" and "Job Title". Can you pls. suggest complete step that can give the desired result from the existing format.

I tried this (but error):

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.FromRecords(List.Transform(Table.ToRecords(Source, {{"Contact name", type text}, {"Job Title", type text}}), (x)=> Record.TransformFields(x, {"Contact name", each Text.Trim(Text.AfterDelimiter(x[Contact name], ".", 0))})))

in
#"Changed Type"

[Vijay_A_Verma]

Use this

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.FromRecords(List.Transform(Table.ToRecords(Table.TransformColumnTypes(Source,{{"Contact name", type text}, {"Job Title", type text}})), (x)=> Record.TransformFields(x, {"Contact name", each Text.Trim(Text.AfterDelimiter(x[Contact name], ".", 0))})))
in
#"Changed Type"

[Excel_Learner]

Still this error:

[Vijay_A_Verma]

Please upload an Excel file with dummy data with the query applied to any file hosting service such as Onedrive, Google drive, Box, Dropbox...and share the link here

[Excel_Learner]

Hi Vijay - here is the data. 

Original Data
Contact name	Job Title	Email
1. Sam Kennedy       2. John Kennedy	1. Software Engineer 2. Manager	sam.kennedy@xxx.com john.kennedy@xxx.com

[v-jingzhan-msft · Accepted solution]

Hi @Excel_Learner 

This is my solution. You may have a try! 

let
    Source = Excel.CurrentWorkbook(),
    Table2 = Source{[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Table2,{{"Contact name", type text}, {"Job Title", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Contact name] <> null and [Contact name] <> ""),
    Custom1 = Table.FromColumns(List.Transform(Table.ToColumns(#"Filtered Rows"), each List.Combine(List.Transform(_, each Text.Split(_, "#(lf)")))), {"Contact Name", "Job Title"})
in
    Custom1

From

into

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!