cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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...

 

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

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?

 

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

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.

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

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

 

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

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,805 guests
Please welcome our newest community members: