String to Number - Multiple Conditions

[renanc]

Hey guys, I want to model a data which is string and turn it to number, however there are some details who might be hard to treat.
The data comes from a chain of strings, like adresses, zip codes, names, and for some rows in this same column I have related values (currency), the ones that I need.

- What I did?
1) First Step, I've changed everything to number, as this image below:
String to Number
And if this wasnt a number it's turned into "null/blanc"

2) I have created a conditional to extract "." and "," - By the image:
Extracting "." and "," from numbers

Why am I even trying to do this instead of just transform into number?
Because I have numbers with multiple conditions whose need to be trated, I'm going to clarify through the other image:
Left column is raw data (string) and the right is string to number over these conditions.
As you can see I have conditions which the interval between numbers starts with "." and "," even there are numbers/strings which have these conditions in the end.

So to resume: Numbers Starting with "." "," and finishing with it.

What I need?
A condition in (M) power query that can complement or change the previous formula that I've created in order to do these conditions, this is related to formula created in image of item: 2).

Thank you guys for your time and help!

[Nathaniel_C]

@renanc ,

Let me suggest that we reach out to @ImkeF who is the M master!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[renanc]

@Nathaniel_C  Thanks for your reply 🙂

@ImkeF  Hey dude, please give me a hand here hahaha =P

[ImkeF]

Hi @renanc ,

You need a pattern here. Would the 3rd last character be fine (if it's a point/dot, then swap dots and commas else convert to number as it is)?

Or is the posted data sample not representative?

[renanc]

@ImkeF  Sorry for the delay, I was sick during these past days. 

Oh man, you're a genius.
Yeah, it is not representative, I've uploaded the wrong image, however, the real one is here, as follows:

Original Data without any transformationAnd the Data with: formula applied from M

Formula:
Obs: "Valor - Copiar" and "Valor" are the same data, it is just a duplicated column.

if Text.Contains([#"Valor - Copiar"], "." ) then Text.BeforeDelimiter ([#"Valor - Copiar"], ",") else Text.BeforeDelimiter ([#"Valor - Copiar"], ".")

So I dont know how can I change this one to get the right formula , there are cases where im stripping out the entire number because of a number is starting with ",", and of course if a change this formula, will happen the same thing when it starts with ".".

I really dont know how to do that properly. 

[ImkeF]

That looks doable, but I need some data to work on.

Could you please paste a link to a file with sample data or at least paste the sample data into the thread so that I can easily copy it.

[renanc]

@ImkeF 
Link to a file:
https://drive.google.com/file/d/15y2J3Ke4nHyLal9fcGibqjZZvPIUZeBS/view?usp=sharing

Necessary Step

You can work on in the step marked as red, the others one were just a play with some trials like removing text, however when im forcing into text removal, the cents are being dropped as units in the steps ahead, for example: 10.000,53 or 10,000.53 = 1000053.

Thank you for your help 🙂

[ImkeF · Accepted solution]

Hi @renanc ,

your data was a bit dirtier than expected and I realized it a bit late. So the code might not be ideal, but it seems to work (see attachment at the end of this message).

Didn't clean it up, as I had to spend more time than expected already...

[renanc]

@ImkeF 

You totally solved my issue, I didn't have a clue to this solution and you did it!!

u don't know how much this is going to help me out now =D

Thank you very much for your time and effort!!

[renanc]

@ImkeF I've made a sample with the same type of data that I sent previously:
Link: https://drive.google.com/file/d/15y2J3Ke4nHyLal9fcGibqjZZvPIUZeBS/view?usp=sharing

Some comments to provide you a little help:
1) String to Number: 10.000,53 ; 10,000.53 ; 10000,35 ; 10000.35 = To reespectively 1000053; 1000053; 1000035; 1000035
(all digits will remain when the string is converted to number).
So I think it is easier to work before with strings than numbers.

2) I dont care with "cents" example: 10.000,53 or 10,000.53 can be = To 10000 && 10000 but not 1000053

3) Last, I did some steps, but the only one that you really need to work is this marked as red in the image bellow:
Step Necessary
The only issue is that after the step 3 is ready, I'll probably dont know how to convert it to number without corrupt 10,000 or 10.000 that's why I said I don't care with cents, because it is better to just don't have it in the convertion, otherwise it will be digits.
Obs: I've added comments inside advanced editor 

Thank you and good luck.