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

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

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"