[M Question]. Split one column into two. Various length space delimiter

[Anonymous]

Hello, 

What is the good M code to break this text column into two columns? 

I was experimenting with Splitter functions but no luck....

Thanks

[Anonymous · Accepted solution]

@Anonymous

You've got a couple of options. If you can guarantee a certain amount of spaces both leading and between your numbers, you can split by number of characters. For example, if there are 8 spaces, a number, and then 8 more spaces, you could split on position 14, assuming your number is never more than 6 digits long.

See screenshot:

Otherwise, you can first trim the column, then split on the first space (leftmost), then an auto-type-detect will get your your numbers:

Original

First Trim

Split on first space

Type detection result

[Anonymous · Accepted solution]

@Anonymous

You've got a couple of options. If you can guarantee a certain amount of spaces both leading and between your numbers, you can split by number of characters. For example, if there are 8 spaces, a number, and then 8 more spaces, you could split on position 14, assuming your number is never more than 6 digits long.

See screenshot:

Otherwise, you can first trim the column, then split on the first space (leftmost), then an auto-type-detect will get your your numbers:

Original

First Trim

Split on first space

Type detection result

[MarcelBeug]

So it looks like you have a bunch of leading, middle and trailing spaces.

Maybe you can split on position and trim the resulting columns?

[Anonymous]

@MarcelBeug

My idea was to extract first character in the first separate column and then second into a second column. 

Or delete leading and trailing spaces, split the by the 1st space and then clean all the spaces 

Does this sound like a good strategy ? 

Thanks

[MarcelBeug · Accepted solution]

It all depends on the possible formats of your data.

Example in he following code. Note: after splitting the column, a "Changed Tyoe"  step was generated, turning the data into numbers; I removed this step. On second thought, that might also be a possibility to change the numbers back to text. Then the spaces will be gone as well.

let
    Source = #table(1,{{"     3          0     "},{"     2          0     "}}),
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 10}, false), {"Column1.1", "Column1.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Position",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}})
in
    #"Trimmed Text"