Results 1 to 5 of 5

Thread: Move lines from one column to another

  1. #1
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    13
    Articles
    0
    Excel Version
    2010

    Move lines from one column to another



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a file with Whatapps conversations and I want it split in two columns, either solution 1 or solution 2.
    See image below.
    I used split column with a delimiter and managed to split some rows.
    The problem is that some rows don't have that delimiter.
    How to transfer lines 4 & 5 from column A to B (solution 1) or better solution 2?
    Thanks for the help.

    Click image for larger version. 

Name:	Whatapps import.png 
Views:	56 
Size:	19.3 KB 
ID:	9358
    Attached Files Attached Files

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    61
    Articles
    0
    Excel Version
    Office 365
    Hi Ronald, below is just one of many ways to do this.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"App", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "App2", each (if Text.Start([App],1) <> "[" then "]" else "" ) & [App]),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "App2", Splitter.SplitTextByDelimiter("]", QuoteStyle.Csv), {"App2.1", "App2.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"App2.1", type text}, {"App2.2", type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,{"App2.1"}),
        #"Filled Down" = Table.FillDown(#"Replaced Value",{"App2.1"}),
        #"Grouped Rows" = Table.Group(#"Filled Down", {"App2.1"}, {{"Message", each Text.Combine([App2.2], "#(lf)"), type text}})
    in
        #"Grouped Rows"

  3. #3
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    13
    Articles
    0
    Excel Version
    2010
    Thank you very much Cyborgski, it works. I Don't know how but I will debug the M code.

  4. #4
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    61
    Articles
    0
    Excel Version
    Office 365
    Sorry Ron, I should have elaborated.

    Add a column that tests to see if the row has a left bracket. If it does not, then we know we are missing a delimiter and will concatenate a delimiter to the front of the field. Now the split will force the text into the second column. The split will leave an empty string "" in the first split column. We replace the empty string with a null value. We can now fill down on the first split column. Finally, we can group by App2.1 and combine the text by the line feed delimiter Text.Combine([App2.2], "#(lf)") to yield the desired result.

    Regards,
    Mike


  5. #5
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    13
    Articles
    0
    Excel Version
    2010
    Thanks Mike, I figured out on my self what you had done. Brilliant. Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •