Forum Discussion
czuniga
Helper III
4 years agoCharacter to number
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 thes...
smpa01
Community Champion
4 years agoczuniga 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"