Forum Discussion
czuniga
4 years agoHelper III
Character 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...
czuniga
4 years agoHelper III
Yes, exactly
- smpa014 years agoCommunity Champion
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"