Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Is it possible to use different Locale for Query and Report?

I have a report where the source uses US dot as decimal separators. When I create my visualization I wish to use my Swedish Locale with space as thousand separator and comma as decimal separator.

 

If I change the file Locale to Swedish my Import Query do not recognize the US formated numbers and returns errors. I could use Power Query to replace dots with commas in number columns and then format them as Swedish numbers but this seems less than ideal.

 

How can I use US Locale in the Queries and Swedish Locale in the visualizations?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thank you for your suggestions. I have found an acceptable solution:

     

    Keep the file as Swedish Locale and when loading a column I can right click and select "Change Type" -> "Using Locale..". This means I can load an individual column with US Locale as needed without search and replace etc.

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for your suggestion. The blog post is great but describes how to change the column headings not the number formats.

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi, Anonymous , interesting issue I've ever seen for the first time, it's all about Culture parameter setting in the system and browser. You might want to try a method mentioned in this article.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for the suggestion but the article state: "This technique requires to process of the model after applying the change to the culture, but there is no way to change the culture preserving the existing data in the model.". This means if I change the locale to Swedish to get the number format right the data has to be loaded again resulting in errors from US locale number formats in source. Essentially the same behavior as changing locale in pbix file settings.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Sorry for that you cannot change it to a number type.

    You can just change it to a text type using FORMAT function.

     

    value measure = 
VAR sales =
        FORMAT ( SUM('Table'[value]), "#,#.## ;" )
    RETURN
SUBSTITUTE ( SUBSTITUTE (SUBSTITUTE ( sales, ".", "@" ), ",", " " ), "@", "," )

     

     

    If you have any question, please kindly ask here and we will try to resolve it.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      Thanks it worked perfectly, I just edited a little the code for the format of my country (chile), to show in USD).

      value measure =
      Where sales=
      FORMAT ( SUM('Table'[value]), "#,#.## ;" )
      RETURN
      if(RIGHT((TRIM("$"& SUBSTITUTE ( SUBSTITUTE (SUBSTITUTE ( sales, ".", "@" ), ",", "." ), "@", "," ))),1)=".",(TRIM("$"& SUBSTITUTE ( SUBSTITUTE (SUBSTITUTE ( sales, ".", "@" ), ",", "" ), "@", "," ))),(TRIM("$"& SUBSTITUTE ( SUBSTITUTE (SUBSTITUTE ( sales, ".", "@" ), ",", "." ), "@", "," ))))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for your suggestions. I have found an acceptable solution:

     

    Keep the file as Swedish Locale and when loading a column I can right click and select "Change Type" -> "Using Locale..". This means I can load an individual column with US Locale as needed without search and replace etc.