Forum Discussion
M: Split column with multiple spaces between fields
One way to do this is to split the text into a list based on the delimiter, remove nulls, and then recombine the list into a string with just a single space between words.
= Table.TransformColumns(Source, {{"Column1", each Text.Combine(List.Select(Text.SplitAny(_, " "), each _ <> "")," "), type text}})or in expanded format
= Table.TransformColumns(
Source,
{{"Column1",
each Text.Combine(
List.Select(
Text.SplitAny(_, " "),
each _ <> ""
)
," "
),
type text
}}
)Then you can split this transformed column by the space delimiter.
One way would be to create a custom column where you swap out the value based on the row number. This assumes you have an index column, but if you don't it's easy to add.
= Table.AddColumn(#"Previous Step", "Custom", each if [Index] = <row number> then <value> else [SourceColumn])
You can either use that column instead or delete the source column and rename the custom column to match the source columns name.
That's great. Thanks very much AlexisOlson
Is there some alternative faster than that you can think of? I'd just want to avoid the renaming of the columns if possible.
Many thanks for your patience
I think you should be able to do a column transform on the column you want to change instead of creating a new one and then renaming, but I was having trouble getting the syntax right when I tried that instead.