Forum Discussion
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 use the Query Editor to split the above in four columns:
C1 C2 C3 C4
1 Red 23 Yellow
i.e., the delimiter is "1 or more spaces".
This would be easy if the Trim function in M worked as the one in Excel but it only seems to remove the trailing spaces, not the ones between words.
How can this be done in M/Query Editor?
Many thanks
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.
11 Replies
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.
Thanks a lot AlexisOlson. It works.
I still have to take a good look to understand well what the code does. I'm not very familiar with M. Might get back to you with some question.
In any case, I was surprised M does not have a function that does this directly, like excel.
Here's what the logic does:
If you have String = "1 Red 23 Yellow", then Text.SplitAny(String, " ") is the list:
{"1","","","","","","","","Red","23","","","","","","","","","","","","","","","","","","","","","","","","Yellow"}
Using List.Select to choose only elements that are not empty strings, "", you get:
{"1","Red","23","Yellow"}
Combining that list back into a string with Text.Combine gives you the final result:
"1 Red 23 Yellow"
this solution worked great for my same issue. Thanks for contributing!
