Extract numbers from a string (before 0)

[okiedokie2017]

Hello, 

I have a column with strings like: 

11.2.0.353

11.2.0.4543

11.5.0.599393

What I am trying to do is to extract the numbers before "0". Meaning: 

11.2

11.3

11.4

11.5

Is there a way I can do it? I initially wanted to use the "replace" function in query and found out that I had to put in every single specific number which is too much. 

[TomMartens]

Hey,

yes there is a way.

In the Query Editor mark the column and choose "Split Column" from the context menu. Be aware that this function replacess the source column with the result of this function. And also the charater(s) that is used will not be present in neither of the resulting columns any longer. If you need this column in your data model first duplicate this column and use the duplicated column instead for the steps I describe.

Then I chooes this setting (be aware that I choose ".0" as a separator.

After this step your table looks like this (of course in my example there has been just one column

Doing its magic the Split column automatically added an additional step "Changed Type", and suddenly the both columns of the split are of datatype number. To change this just mark the this step and you will see this M function in the Formula Bar (Menu View: Enable Taskbar if not)

= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SomeStrings.1", type number}, {"SomeStrings.2", type number}})

To fix this change the type to text like so

= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SomeStrings.1", type text}, {"SomeStrings.2", type text}})

This results to the following

And there you are, now you can remove the column you don't need anymore

Hope this is what you are looking for

Regards

Tom

[MarcelBeug · Accepted solution]

Alternatively, you can extract text before a delimiter (I would use ".0.", just in case there are 0's in the part you want to keep):

[TomMartens]

Hey @okiedokie2017,

the solution of @MarcelBeug is much shorter (and hence smarter) than mine, I always forget about the "Extract" function.

Regards

Tom