Need help on power Query split to column function

[spycein]

Hello Everyone,
I am looking for a split to column function which splits the value in multiple value on occurrence of starting with numbers i.e numbers between 0-9.
For example, i have a data like following column

Column 1

1 07-Feb-2020 1,275 194H - Commission / Brokerage

I am looking for a function which would return the result as follows

Column 1	Column 2	Column 3	Column 4
1	07-Feb-2020	1,275	194H - Commission / Brokerage

Hope i have explained my query properly.
Thank you so much in advance.
Best Regards,
Shib

 

[alansidman]

If all your data is representative of your sample then

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column 1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column 1.1", "Column 1.2", "Column 1.3", "Column 1.4", "Column 1.5", "Column 1.6", "Column 1.7", "Column 1.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column 1.1", Int64.Type}, {"Column 1.2", type date}, {"Column 1.3", Int64.Type}, {"Column 1.4", type text}, {"Column 1.5", type text}, {"Column 1.6", type text}, {"Column 1.7", type text}, {"Column 1.8", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Column 1.4", "Column 1.5", "Column 1.6", "Column 1.7", "Column 1.8"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

[GraH]

Variation of the same. I'm sure there is a shorter way though.

let
    Source = Excel.CurrentWorkbook(){[Name="Text"]}[Content],
    TxtAsDelimitedList = Table.AddColumn(Source, "TextAsList", each { Text.Replace(Text.BeforeDelimiter([Text]," ", Occurrence.Last+1), " ", "|") & "|" & Text.AfterDelimiter([Text], " ", Occurrence.Last+1) } ),
    TxtAsTable = Table.AddColumn(TxtAsDelimitedList, "TextAsTable", each Table.FromList([TextAsList], Splitter.SplitTextByDelimiter( "|", QuoteStyle.None))),
    KeepTxtAsTable = Table.SelectColumns(TxtAsTable , {"TextAsTable"} ),
    ExpandTextAsTable = Table.ExpandTableColumn(KeepTxtAsTable, "TextAsTable", Table.ColumnNames(KeepTxtAsTable[TextAsTable]{0}))
in
    ExpandTextAsTable

 

[spycein]

Thank you so much for your inputs. Found a much easier way to solve this problem i.e. Split column by position function.
Best Regards,
Shib

 

[alansidman]

Show us your Mcode, please. It will be helpful for others who may be seeking a similar answer.

 

[sandy666]

I think it will be similar to this
= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 2, 13, 19}))

 

[alansidman]

Thanks Sandy. Hoping the OP has a consistent cell accountability, ie. Length and spacing?

 

[sandy666]

OP's example isn't representative but should be...
I will not write what I think about it

 

[sandy666]

I did it with Length, Running Sum and some details
but I'll wait for OP's solution

 

[spycein]

Hello Everyone,
Sorry for the delay in response.
here is m code which i used

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Positions" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByPositions({0, 5, 21, 31, 34}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Column1.1", Int64.Type}, {"Column1.2", type date}, {"Column1.3", Int64.Type}, {"Column1.4", Int64.Type}, {"Column1.5", type text}})
in
#"Changed Type1"

Then i merged the Column 1.4 & 1.5 as column 4

THanks