Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Multiple Region Number formats in a Single Column

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!

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

NewStep=Table.TransformColumns(PreviousStepName,{"ColumnName",each let a=Text.SplitAny(Text.From(_),",.") in Number.From(Text.Combine(List.ReplaceRange(a,List.Count(a)-1,0,{"."})),"en-US")})

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thanks a lot, we were struggling a lot with this issue, you saved us!

wdx223_Daniel
Super User
Super User

NewStep=Table.TransformColumns(PreviousStepName,{"ColumnName",each let a=Text.SplitAny(Text.From(_),",.") in Number.From(Text.Combine(List.ReplaceRange(a,List.Count(a)-1,0,{"."})),"en-US")})

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors