How to split a column based on values

[Anonymous]

I am looking to split a column, (pictured below) but I only need to split certain values in the column. 
Column to be split
If the value starts with 1 I would like a split at the space, But values that do not start with 1 I would like to retain the value. 


Example Current State

Resources

1DP01-01 - Operator 1

Battery Lines

Example Desired State

Resources	Resoucres Split
1DP01-01	- Operator 1
Battery Lines	Battery Lines

Thanks!

[AlB]

Hi @Anonymous 

Best in the Query Editor. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnQJMDDUNTBU0FXwL0gtSizJL1IwVIrViVZySiwpSS2qVPDJzEstVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resources = _t]),
    auxT1_ = Table.SelectRows(Source, each Text.Start([Resources],1) = "1"),
    auxT2_ = Table.SelectRows(Source, each Text.Start([Resources],1) <> "1"),
    res1_ = Table.SplitColumn(auxT1_, "Resources", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Resources1", "Resources2"}),
    #"Added Custom" = Table.AddColumn(auxT2_, "Resources2", each [Resources]),
    res2_ = Table.RenameColumns(#"Added Custom",{{"Resources", "Resources1"}}),
    res_ = Table.Combine({res1_, res2_}) 
    
 in
    res_

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[amitchandak]

@Anonymous , I you may have to use two-three steps in power bi.

Split into column based on last occurrence -

you will get Column 1, column 2

column 3 = if [column2] = null then [column1]  else [column2]