Split a column containing specific intervals

[Anonymous]

Hi all,

I'd like some help on splitting a column into two columns containing specific data. Right now, the column looks like this:

after the numerals, there's specific abbreviations: ST & KG. 

I'd like to split the column into two columns One containing all the data with ST and one containing all the data with KG. 

Can anyone help me make this happen?

Much obliged!

[AlB · Accepted solution]

@Anonymous 

Looks like your current last step is called  #"Removed Columns2". If so, you'd need to add the steps after (in the Advanced editor), making sure the first step "col_" reads from the correct step and column:

    col_ =  #"Removed Columns2"[Column7], 
    KG_col = List.Select(col_, each Text.Contains(_, "KG")),
    ST_col = List.Select(col_, each Text.Contains(_, "ST")),
    res_ = Table.FromColumns({KG_col, ST_col}, {"KG", "ST"})
in
    res_

If this does not work, do share your pbix

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.

[AlB]

Hi @Anonymous 

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

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTwdleK1YlWMtIzUggOATPBhDFMwkTPBEXCFCZhBhaOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    col_ =  #"Changed Type"[Column1], 
    KG_col = List.Select(col_, each Text.Contains(_, "KG")),
    ST_col = List.Select(col_, each Text.Contains(_, "ST")),
    res_ = Table.FromColumns({KG_col, ST_col}, {"KG", "ST"})
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.

[Anonymous]

Hi, 

Many thanks for the quick response! Would you be willing to go a little more in-depth as to what and where I have to place the M code to get my desired outcome (I'm very new to this)?

This is the situation as a whole.

Thanks again!