How to split a column and only keep specific content

[padfootkk]

Hi everyone,

I have a column that contain several values in it, the values are seperated by a simicolon and a space. Now I just want to create another column that only keep the numbers that after the word "item". For those rows that have more than one item numbers, keep the first one; for those rows that don't have a item number, then fill the new column as "not applicable".

Example:

Table

ID	Column1	Column2
123	text; item 123456789	123456789
234	word 2.3; item 398475921	398475921
345	item 123345204; item 102948329	123345204
456	text; word 2.3	Not Applicable
567	text	Not Applicable
678	item 104924203	104924203

I know the table is kind of messy, does anyone have ideas about this?

Thanks!

[Ashish_Mathur · Accepted solution]

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.

[v-frfei-msft · Accepted solution]

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"

[Ashish_Mathur · Accepted solution]

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.

[amitchandak]

Please refer . In case you are using new office Ribbon , then use transform data under the home tab of DATA view

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

https://community.powerbi.com/t5/Desktop/Splitting-multiple-columns-using-delimiter/td-p/438358

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[HotChilli]

In Power Query, you could split the column on "item" by right-clicking on the column, choose "Split column"-> by delimiter, Choose Custom and enter the word item.

You'll get something similar to this in the advanced editor:

= Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({"item"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"})

and you can change the null values in your data by using 'Replace Values' to put 'Not applicable'