• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Region Number formats in a Single Column

emilianoalvz

New Member
Hello, I have a file that runs a macro, and then downloads some SAP records.
Then a query retrieves this data, but the thing is that when it brings the data, depending on the user's region, it could be downloaded as "en-US" which is american format for numbers, or it could be downloaded as European format.
The column can have up to 2 different number formats:

  1. american format: commas for thousands and points for decimals, (1,240.22) or viceversa
  2. european format: points for thousands and commas for decimals. (1.240,22)
The column at first has a Text data type, and then when I transform it into Decimal data type, I get an error for the European records.
--> What I want to do is to standardize this into a single format, in this case, American Format "en-US".
I was thinking maybe with one of these formulas: Number.FromText () or Number.From() but I'm not being able to because
Maybe if I combine that function with a List function it could turn out.
I'm not profficient with M language so I could use a little help for this.
emilianoalvz_0-1656644042614.png

What I wanted with this approach is to create an error-proof step, so no matter where the user is from, that in the end the data is standardized into a single format.

Has anyone had this type of error before?
I don't know if my approach is correct or if there's a better way to solve this issue; to standardize multiple number formats of a column into a single format. Actually I want to apply this for several columns but knowing how to solve it for one, I can replicate that to the other necessary columns.

Thanks!
 
There are number of ways to handle it.
1. Use Text.Split() by comma and check for Text.Length() of List.Last(SplitResult). If = 2 then replace comma with period and vice versa on original string.
2. Use try... otherwise to handle error using implicit conversion to number. If error, perform Text.Replace().

etc.
 
Back
Top