Character to number

[czuniga]

I have a column where the values look like ////XXX///. The "/" represent 5 minutes for one provider and the "X" represents 15 min for another.  How can I create a new column with the actual time these characters represent?

[smpa01]

@czuniga  so ////XXX/// should equate to 80 minutes ?

[czuniga]

Yes, exactly

[smpa01]

@czuniga  would be happy with a PQ solution

let
   regex=let   fx=(input)=>
    Web.Page(
        "<script>
            var x='"&input&"';
            var b =(x.match(/\//gm)).length*5+(x.match(/x/gmi)).length*15;                                 document.write(b);
        </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text]

in
fx,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0geCiIgIIKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each regex([Column1])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p...

[czuniga]

Thank you for this. I'm not super familar with using the advanced editor. What would I use if I just wanted to add a column to an existing workflow?

[smpa01 · Accepted solution]

@czuniga  you can simply do this

[Pragati11]

HI @czuniga ,

Not sure what you are trying to ask here. Please add more details here like screenshots, some sample data, etc. ?

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Thanks,

Pragati

[czuniga]

Just edited!