Forum Discussion

RacAgr's avatar
RacAgr
Icon for Helper I rankHelper I
9 years ago
Solved

Thousand-Separator-and-Decimal-Separator

hello,

 

for the currency column, I'm having problems with decimal separator and thousand separator.

 

Decimal separator is a comma and thousand separator is a point. But PowerBi goes wrong

 

for example - powerbi shows 121,000.00 (thousand separator is a comma and Decimal separator is a point)

 

Output expected, 121.000,00 (Decimal separator is a comma and thousand separator is a point)

 

Thanks

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    The display of numeric felds (and dates, times etecetera) depends on your Regional Settings.

     

    It can be adjusted in Power BI Desktop or in Windows (Land/Region). It looks like the Windows setting is leading, but you have to close and reopen your pbix file after adjusting the Regional Settings.

     

     

    In Power BI you can also adjust your language settings to influence the display format for published reports.

     

     

16 Replies

  • Jasper_Eggink's avatar
    Jasper_Eggink
    Regular Visitor
    7 years ago

    Helle all,

     

    Thanks to this blogpost I have found a sulution for this problem without changing the locale for the whole Power Bi file. The steps are:

     

    1. Go to the Power Query-editor and select the column
    2. Change the data type to "decimal number" in the transform menu
    3. Open the advanced editor and add the desired locale string to the "Table.TransformColumnTypes" function.

    Example:

     

    #"Type gewijzigd" = Table.TransformColumnTypes(#"Kolommen verwijderd",{{"sales.grand_total_month", type number}}, "en-US")

    • fernandopj's avatar
      fernandopj
      New Member
      3 years ago

      This is the right answer. I don't care about Regional Display, I want the Power Query to correctly parse my column as it is written.

    • RacAgr's avatar
      RacAgr
      Icon for Helper I rankHelper I
      9 years ago

      any suggestions pls on how to achieve the expected output?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi RacAgr,

     

    Based on test, if I export these data to csv, it will remove custom format and keep the normal number. So I think you store these value with text format, right?

     

    Regards,

    Xiaoxin Sheng

    • RacAgr's avatar
      RacAgr
      Icon for Helper I rankHelper I
      9 years ago

      these values are in decimal number then using currency 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi RacAgr,

         

        I can't reproduce your issue, It works well on my side.

         

        Regards,

        Xiaoxin Sheng

  • Mahashankari's avatar
    Mahashankari
    New Member
    7 years ago

    The Thousand Separator could not work, if the currency value contains more than 8 digits. Provide solution for this. 

  • Deepikasimha's avatar
    Deepikasimha
    Frequent Visitor
    6 years ago

    Hello,

     

    We have a power bi report embedded int .net application. We have given ($)United States format for one column in the report. It shows $123,345,678(Right format) in few browsers and machines and $12,33,45,678(wrong format) in few browsers.

     

    Can some one help me to fix this please.

     

    Thanks,

    Power BI Learner