How to split a column and only keep specific content

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column1", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Column1], "item "), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","Not Applicable",Replacer.ReplaceValue,{"Text After Delimiter"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Text After Delimiter", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Text After Delimiter.1", "Text After Delimiter.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Text After Delimiter.2"})
in
    #"Removed Columns"

Hope this helps.

Hi padfootkk ,

 

Please check the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bU5LDoIwEL1K0zUhZTpIJ668gBdoukDtggQDMU30+D6wpRt37zfznve6I6sbneInndWU4lNB4P40OIFccWi8BoH2Xl4PRa3NcSuOh16og1XxFscptPITjAyXDkPCzlLu+Hn7EeqOOaUJwnVJ6rKu83Qfb3Pck9iVk/99zD7qDQsxme1TxSF8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("item", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Text.Select([Column1.2],{"0".."9"})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom] = null then "Not Applicable" else [Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Column1.2", "Column1.3", "Column1.1"})
in
    #"Removed Columns"