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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Still this error:

 

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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!