M: Split column with multiple spaces between fields

[AlB]

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

[AlexisOlson · Accepted solution]

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.

[JoeRobert06]

this solution worked great for my same issue. Thanks for contributing!

[AlB]

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.    

[AlexisOlson]

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"

[AlB]

@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]

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.

[AlB]

---

@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]

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.

[AlB]

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 

[AlexisOlson]

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.

[AlB]

Thanks very much @AlexisOlson