Forum Discussion
Split word into Delimiter not functioning properly
- 4 years ago
What delimiter are you splitting on? It looks like you might be splitting on space " " rather than a line feed.
If you use the GUI to split your text column, it will produce a step that looks like this:
= Table.ExpandListColumn( Table.TransformColumns( Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType} }}), "Column1")You can ignore most of that. Just focus on the Splitter part. Replace this
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)with this
Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv)if you want to split on spaces and line feeds.
Here's a full sample M query you can paste into the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLjHVi8mLyUNnKcXqRCu55pWkFikU5+emKhRlJmcolKRWlChkpBalQqTT8ov0kABIc0lmbqpSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1") in #"Split Column by Delimiter"
What delimiter are you splitting on? It looks like you might be splitting on space " " rather than a line feed.
If you use the GUI to split your text column, it will produce a step that looks like this:
= Table.ExpandListColumn(
Table.TransformColumns(
Source,
{{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}}), "Column1")
You can ignore most of that. Just focus on the Splitter part. Replace this
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)
with this
Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv)
if you want to split on spaces and line feeds.
Here's a full sample M query you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLjHVi8mLyUNnKcXqRCu55pWkFikU5+emKhRlJmcolKRWlChkpBalQqTT8ov0kABIc0lmbqpSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
in
#"Split Column by Delimiter"