Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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"
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"
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