Forum Discussion
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); however, I'd like to extract the names and break them into two new columns.
I can't exactly use a character count because the name characters vary. Is there a way to assess by second line?
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"
3 Replies
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"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)
NameWhen 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.
