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 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"
Two additional questions:
1. For your solution, is it necessary to write the code directly in M (in the editor) or can this be done by using a combination of the steps in the menus?
2. Since you seem well versed in M, do you know how to change the value of a single cell in a table in the query editor
Thanks very much for your time
- I modified steps I created with the GUI and added functions that I found in the function reference that looked useful. (Power Query M Function Reference) I don't think you could reproduce this particular code via the GUI, but there are other ways of tackling the problem with just the menu buttons that could potentially work. (E.g. Split By Delimiter > Transpose Table > Filter out blank rows > Transpose back)
- There are some awkward ways to transform individual cells, but I'd recommend only doing that as a last resort if you can't find a better method to process your data.
Thanks very much AlexisOlson