How to Extract 3 columnns from a column

[kevinbala]

Hi All,

I need some help with the data im working on, I'm trying to breakdown a column into three new columns on PowerBI

 

Conditions

1. if the characters starts with a Letter, it moves to the Left, else leave it blank
2. If the character ends with a W, then move it to the Right
3. extract the numbers in between
4. get rid of the '%'

[Vijay_A_Verma · Accepted solution]

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("i45WcrEwC1eK1YlWcjQxhdAWFhABCwtVqIApVIWZGZg2N4DwLS1VoQohlCVEGGJiLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Default Header" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Actual%", each Text.Select([Default Header],{"0".."9"}), type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Left", each if List.Contains({"A".."Z"},Text.Start([Default Header],1)) then Text.Start([Default Header],1) else null, type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Right", each if Text.EndsWith([Default Header],"W") then "W" else null, type text)
in
    #"Added Custom2"