how to extract column values on basis of certain characters and creating new column?

[DivyanshuTotla]

Basically i am having column as "Win By" which contain the info about the team win. so i want to extract the column values into two columns which is "Win By Run" and "Win by Wicket". the output is given below

Input:

Win By	Win By Run	Win By Wicket
8 Runs
7 wickets
20 Runs

output:

Win By	Win by Runs	Win by wickets
8 Runs	8
7 wickets		7
20 Runs	20

[Vijay_A_Verma · Accepted solution]

Use these two formulas in two custom columns

= if Text.Contains([Win By],"Runs", Comparer.OrdinalIgnoreCase) then Text.Split([Win By]," "){0} else null

= if Text.Contains([Win By],"Wickets", Comparer.OrdinalIgnoreCase) then Text.Split([Win By]," "){0} else null

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslAIKs0rVorViVYyVyjPTM5OLYHwjAygMrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Win By" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Win by Runs", each if Text.Contains([Win By],"Runs", Comparer.OrdinalIgnoreCase) then Text.Split([Win By]," "){0} else null, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Win By Wickets", each if Text.Contains([Win By],"Wickets", Comparer.OrdinalIgnoreCase) then Text.Split([Win By]," "){0} else null, Int64.Type)
in
    #"Added Custom1"

[HotChilli]

Remove the columns 

Win By Run

Win By Wicket

 (maybe they are just there as an example anyway)

--

Split the 'Win By' column on space. Then Pivot the column that has the runs/Wickets  type in it.

You'll need a unique identifier on each row - maybe you have this already, if not add an index before the Pivot