cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

String to Number - Multiple Conditions

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 NumberString 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 numbersExtracting "." 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.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!

1 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...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
Super User I
Super User I

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Nathaniel_C  Thanks for your reply 🙂

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

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?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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 transformationOriginal Data without any transformationAnd the Data with: formula applied from MAnd 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. 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

Necessary StepNecessary 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 🙂

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...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

@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!!

@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 NecessaryStep 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.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors