cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AlexisOlson Member
Member

Re: 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.

10 REPLIES 10
AlexisOlson Member
Member

Re: 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.

Super User
Super User

Re: M: Split column with multiple spaces between fields

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.    

Super User
Super User

Re: M: Split column with multiple spaces between fields

@AlexisOlson

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

AlexisOlson Member
Member

Re: M: Split column with multiple spaces between fields

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"

 

AlexisOlson Member
Member

Re: M: Split column with multiple spaces between fields

  1. 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)
  2. 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.
Super User
Super User

Re: M: Split column with multiple spaces between fields

Thanks very much @AlexisOlson

Super User
Super User

Re: M: Split column with multiple spaces between fields


@AlexisOlson wrote:
2.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.

 

@AlexisOlson

I agree it's not a good approach in general but I just want it to change values quickly and directly in Power BI when I am testing something with dummy data. Could you show how to do it? Thanks 

AlexisOlson Member
Member

Re: M: Split column with multiple spaces between fields

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.

Super User
Super User

Re: M: Split column with multiple spaces between fields

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