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.  If the value starts with 1 I would like a split at the space, But values that do not st...
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.