Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to split a column based on values

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

ResourcesResoucres Split
1DP01-01- Operator 1
Battery LinesBattery Lines


Thanks!

 

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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]

     

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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.