Comparing versions with multiple decimal points

[algorton]

Good morning

I am trying to run a comparision to check if the operating system version of my mobile devices is compliant. I was thinking of running Power Query e.g.

if [operatingSystem] = "Android" and [osVersion] <=10
then "TRUE"
else if [operatingSystem] = "iOS" and [osVersion] <15
then "TRUE"
else if [operatingSystem] = null or [osVersion] = null
then "Unknown"
else "FALSE"

My data is obviously in the incorrect format so the idea was to convert to decimal (I only need the major version number to do a comparison):

When converting the column to decimal I see the following error: DataFormat.Error: We couldn't convert to Number.Details: 5.1.1.

Obviously the multiple decimal points seem to be causing a problem; numbers with a single decimal point e.g. 7.0 convert with no issue. It would be appreciated if anyone could provide me some ideas on how I can achieve this comparision.

Thanks

[collinsg · Accepted solution]

Good day @algorton 

The Text.BeforeDelimiter function may be used to extract text before the first "." (if there is no "." it will simply return the text). In the following code replace #"Previous Step" with the name of your previous step.

= Table.TransformColumns(
     #"Previous Step", 
     {{"osVersion", each Text.BeforeDelimiter(_, "."), type text}}
   )

After this you can change the column type to whole number.

Hope this helps.

[algorton]

@collinsg thanks very much for your input; the code below worked perfectly for me.

= Table.AddColumn(#"Expanded ownerTypes", "osVersion_Decimal", each Text.BeforeDelimiter([osVersion], "."), type text)