How to localize date fields?

[TimoRiikonen]

The old SSRS supported custom type and that could be used to do the localization on dates and currencies.

But Power BI has been more difficult.

Here is one case example attached, please instruct how to get this working as planned.

Desktop versions used: 2019-09 (English) and 2019-12 (Finnish).

Target: I want to have dates in fi-FI locale settings, for example "31.12.2020" or "31. joulukuuta 2020".

Done:

1) I have changed File -- > Options and Settings -- > Options -- > Current file -- > Regional Settings to Finnish (Finland)

2) I get similar results with English and Finnish versions of Desktop: Finnish version changes automated heading, but not the actual content itself: 

3) Localization settings are used when the data is being read. My data is not in database, so perhaps this is a problem, but at least I have pressed successfully Refresh button.
4) Ideally that should be enough to change the results, but I have also manually localized the column in Powerquery side to Finnish locale: 

(with UI: select edit queries, right click column, chage type, using locale...)

#"Changed Type with Locale" = Table.TransformColumnTypes(Result, {{"Local timestamp with offset", type datetimezone}}, "fi-FI"),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Local timestamp without offset", type datetime}}, "fi-FI"),
#"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale1", {{"UTC date", type date}}, "fi-FI"),
#"Changed Type with Locale3" = Table.TransformColumnTypes(#"Changed Type with Locale2", {{"UTC timestamp", type datetime}}, "fi-FI")

But still the end result is in English.

What to do?

This file is public, so I can upload it if you instruct me how.

[TimoRiikonen · Accepted solution]

Maybe it is against the rules to answer to my own question, but I found the solution:

- The actual report shows properly either English or Finnish depending on the personal server settings: General -- > Language -- > <your locale>

(see https://app.powerbi.com/groups/me/settings/general )

- Desktop perhaps sometimes uses the locale from Windows and sometimes the locale I have defined to Desktop, so if this would trouble me, I could try to change my Windows locale.

[TimoRiikonen · Accepted solution]

Localization

Users

• Change your personal server settings General -- > Language -- > <your locale>

Example link: https://app.powerbi.com/groups/me/settings/general?redirectedFromSignup=1

Development

• Locale settings inside Desktop are determined from more than one place:
  1. Windows settings
  2. Default regional settings
  3. Locale settings of the current file
  4. NOTE: Desktop language is irrelevant from 2019-12 onwards. You can overwrite all of the language settings.
• To change the last two, open your report with Desktop
• From desktop version 2019-12 onwards, open File -- > Options and Settings -- > Options -- > Global -- > Regional Settings and here you have two options.

For English speakers I recommend that you change the model language only.

Non-English speakers may use the same or if they wish to see inconsistent translations then they could use the settings on below:

Example case “Latest UTC date” looks good heading in English, but bad in Finnish “Viimeisin UTC date”

Note: This setting only affects new reports, not existing reports!

• “Global” settings don’t change everything, you may also need to alter file based settings.

Select File -- > Options and Settings -- > Options -- > Current file -- > Regional Settings -- > Locale for import -- > <your locale>

• Refresh your data!
• Note that this localization changes currency as well. So if the actual currency is in US$, these settings will show it as Euros. As Power BI doesn’t support custom types the way SSRS does, you may need to change the column as text format to prevent any changes on currency.
• That should be enough, but if you have a special case, then you may need to convert the locale with Power Query. Example case:

#"Content" = Table.TransformColumnTypes(#"Localized content", {{"Local timestamp without offset", type datetime}}, "fi-FI"),

[TimoRiikonen · Accepted solution]

I wrote this as a blog article to here:
https://community.powerbi.com/t5/Community-Blog/Localization-in-Power-BI/ba-p/952439

[TimoRiikonen · Accepted solution]

Localization

Users

• Change your personal server settings General -- > Language -- > <your locale>

Example link: https://app.powerbi.com/groups/me/settings/general?redirectedFromSignup=1

Development

• Locale settings inside Desktop are determined from more than one place:
  1. Windows settings
  2. Default regional settings
  3. Locale settings of the current file
  4. NOTE: Desktop language is irrelevant from 2019-12 onwards. You can overwrite all of the language settings.
• To change the last two, open your report with Desktop
• From desktop version 2019-12 onwards, open File -- > Options and Settings -- > Options -- > Global -- > Regional Settings and here you have two options.

For English speakers I recommend that you change the model language only.

Non-English speakers may use the same or if they wish to see inconsistent translations then they could use the settings on below:

Example case “Latest UTC date” looks good heading in English, but bad in Finnish “Viimeisin UTC date”

Note: This setting only affects new reports, not existing reports!

• “Global” settings don’t change everything, you may also need to alter file based settings.

Select File -- > Options and Settings -- > Options -- > Current file -- > Regional Settings -- > Locale for import -- > <your locale>

• Refresh your data!
• Note that this localization changes currency as well. So if the actual currency is in US$, these settings will show it as Euros. As Power BI doesn’t support custom types the way SSRS does, you may need to change the column as text format to prevent any changes on currency.
• That should be enough, but if you have a special case, then you may need to convert the locale with Power Query. Example case:

#"Content" = Table.TransformColumnTypes(#"Localized content", {{"Local timestamp without offset", type datetime}}, "fi-FI"),

[TimoRiikonen · Accepted solution]

Maybe it is against the rules to answer to my own question, but I found the solution:

- The actual report shows properly either English or Finnish depending on the personal server settings: General -- > Language -- > <your locale>

(see https://app.powerbi.com/groups/me/settings/general )

- Desktop perhaps sometimes uses the locale from Windows and sometimes the locale I have defined to Desktop, so if this would trouble me, I could try to change my Windows locale.