Forum Discussion
Solved
M: Split column with multiple spaces between fields
Hi all, Imagine we have a column in which we have different fields separated by an arbitrary number of spaces. Example: Column "1 Red 23 Yellow" and we want to...
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.
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.