cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
renanc Frequent Visitor
Frequent Visitor

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:
HELPER-IMG2.PNGString 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:
HELPER-IMG1.PNGExtracting "." 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:
string to number.PNGLeft 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

Accepted Solutions
Super User
Super User

Re: String to Number - Multiple Conditions

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

 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

9 REPLIES 9
Nathaniel_C Super Contributor
Super Contributor

Re: String to Number - Multiple Conditions

@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 Frequent Visitor
Frequent Visitor

Re: String to Number - Multiple Conditions

@Nathaniel_C  Thanks for your reply 🙂

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

Super User
Super User

Re: String to Number - Multiple Conditions

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?

 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




renanc Frequent Visitor
Frequent Visitor

Re: String to Number - Multiple Conditions

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

STN2.PNGOriginal Data without any transformationSTN3.PNGAnd 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. 

Super User
Super User

Re: String to Number - Multiple Conditions

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.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




renanc Frequent Visitor
Frequent Visitor

Re: String to Number - Multiple Conditions

@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 to work.PNGStep 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.

renanc Frequent Visitor
Frequent Visitor

Re: String to Number - Multiple Conditions

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

 

Step to work.PNGNecessary 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 🙂

Super User
Super User

Re: String to Number - Multiple Conditions

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

 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

renanc Frequent Visitor
Frequent Visitor

Re: String to Number - Multiple Conditions

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)