Forum Discussion

avo_om2134's avatar
avo_om2134
Frequent Visitor
2 years ago
Solved

How to shift additional text in column to new column?

Forgive me - I'm quite new to this!    Example: Jane Doe John Smith Jane Doe John Smith Jada Holmes Mo Higgins Jada Holmes Mo Higgins Both names are in the same column (Column 1)...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi avo_om2134 
    When you use the Split Column function, you can specify a custom character to identify Line Breaks.  The notation in Power Query is #(lf) [or #(cr) if it does work].  But you might need to be careful as the Power Query wizard might think you are trying to adding this text string "#(#)(lf)" instead of "#(lf)"

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMS1VwyU+NyfPKz8hTCM7NLMlQitUBSaQkKnjk5+SmFsfk+eYreGSmp2fmFSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Column" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Original Column", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Original Column.1", "Original Column.2"})
in
    #"Split Column by Delimiter"

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi avo_om2134 
When you use the Split Column function, you can specify a custom character to identify Line Breaks.  The notation in Power Query is #(lf) [or #(cr) if it does work].  But you might need to be careful as the Power Query wizard might think you are trying to adding this text string "#(#)(lf)" instead of "#(lf)"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMS1VwyU+NyfPKz8hTCM7NLMlQitUBSaQkKnjk5+SmFsfk+eYreGSmp2fmFSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Column" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Original Column", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Original Column.1", "Original Column.2"})
in
    #"Split Column by Delimiter"

 

 

avo_om2134's avatar
avo_om2134
Frequent Visitor
2 years ago

Thank you so much - I appreciate it. That worked. Good call out on the "#(#)(lf)" - that absolutely happened.

 

One more question - do you know how to remove additional line breaks in the column?

It is currently structured as:
Name
#(lf)
#(lf)
Name

 

When I try the above, a blank pulls over as that is what's next. I discovered when it's just the name beneath the other name it works correctly as you instructed above.