Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Excel Column Splitting

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 th...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    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!

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Vijay - here is the data. 

Original Data
Contact name       Job Title        Email
1. Sam Kennedy      
2. John Kennedy    		1. Software Engineer 2. Manager        [email protected]
[email protected]
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    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!