How to change character used to seperate thousands and decimals ?

[madhushree]

How to change character used to seperate thousands and decimals ?

For example:

In general we use commas : 43,987.09

But i need to convert it to 43.987,09 in power BI.

How can this be done?

[pschommer]

You changed the requirements a little bit. Here's what I did to solve your problem:

OriginalDec is the decimal number in the source file. It's the only column that is native to the data source. The other values are added columns.

OriginalTxt = FORMAT([OriginalDec],"###,###,###.#####")                      'modify to fit the largest number in your data

Millions = INT([OriginalDec]/1000000)

Thousands = INT([OriginalDec]/1000) - IF([Millions]>0,[Millions]*1000,0)

Ones = INT([OriginalDec] - (INT([OriginalDec]/1000)*1000))

Decimal = IF(RIGHT([OriginalTxt],1)=".","",RIGHT([OriginalTxt],LEN([OriginalTxt])-SEARCH(".",[OriginalTxt])))

It was easier for me to visualize and test by leaving the sections of the new "number" as separate columns.

The final result is a string concatenating the above parts together.

NewTxt = IF([Millions]>0,[Millions] & ".","") & IF([Thousands]>0,[Thousands] & ".","") & [Ones] & IF([Decimal]<>"","," & [Decimal])

That worked with examples you provided and a couple I threw in.

[Eric_Zhang]

---

@madhushree wrote:

How to change character used to seperate thousands and decimals ?

For example:

In general we use commas : 43,987

But i need to convert it to 43.987 in power BI.

How can this be done?

---

@madhushree

The locale setting determins how the thousands and decimals seperator show.

• To have that format in Power BI Desktop, you'll have to change the locale setting of your PC.
• To have that format in Power BI Service, change the language(In Power BI Service->setting ->Language).
  eg, in my test case, I changed my Power BI Service language to nederlands, you'll have to change it to your language which has that format. See a snapshot in Power BI Service.
  

[madhushree]

Hi ,

If i change the language then whole GUI languages also changes, which i dont want.

Thanks,

Madhushree

[pschommer]

You can certainly do some string manipulation to format it the way you want, but you will completely lose the value, as @vanessafvg eluded to.

In Excel it would look something like this:

=INT(A1/1000) & "." & A1-(INT(A1/1000)*1000)

where A1 is a column name in Power BI.

This works for all numbers if you only want one period in the result. It will only work for numbers up to 999,999 if you want multiple periods to substitute for commas. The formula will get messier if you want multiple periods.

[pschommer]

In order to always get 3 digits after the decimal point, the formula would have to be altered slightly:

NewValue = INT([DecimalValue]/1000) & "." & FORMAT([DecimalValue] - (INT([DecimalValue]/1000)*1000),"000")

[madhushree]

Hi

I also need the format to be like this:

1.04 ---> 1,04

Thanks

Madhushree

[vanessafvg]

@madhushree when you say how can you convert it, are you wantign to change the value of it to a decimal or are you wanting to change the format?