Hello everyone,
I have a process that generates variety of CSV files with datetime values. The process uses local locale settings to write datetime values in "ShortDate LongTime" format.
When executed on machine with en-US locale set as default produce this format:
Date Format: M/d/yyyy
Time Format h:mm:ss tt
Value Produced:
10/11/2018 9:00:02 PM
9/12/2018 9:51:36 AM
When executed on machine with en-AU locale set as default produce this format:
Date Format: d/MM/yyyy
Time Format: h:mm:ss tt
Values Produced
14/11/2018 7:00:00 AM
13/11/2018 10:00:00 PM
The CSV files are loaded into PowerBI using templates (.pbit file).
Here is the query I use:
let
Source = Csv.Document(File.Contents(ReportDataFolderPath & "\EVT\events.csv"),[Delimiter=",", Columns=27, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Controller", type text}, {"ApplicationName", type text}, {"EventID", Int64.Type}, {"Occurred", type datetime}, {"OccurredUtc", type datetime}, {"Summary", type text}, {"Type", type text}, {"SubType", type text}, {"Severity", type text}, {"TierName", type text}, {"NodeName", type text}, {"MachineName", type text}, {"BTName", type text}, {"TriggeredEntityType", type text}, {"TriggeredEntityName", type text}, {"TriggeredEntityID", Int64.Type}, {"ApplicationID", Int64.Type}, {"TierID", Int64.Type}, {"NodeID", Int64.Type}, {"MachineID", Int64.Type}, {"BTID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "ApplicationIDUnique", each [Controller] & "/App=" & Text.From([ApplicationID])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"ApplicationIDUnique", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "TierIDUnique", each [Controller] & "/App=" & Text.From([ApplicationID]) & "/Tier=" & Text.From([TierID])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"TierIDUnique", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "EventIDUnique", each [Controller] & "/App=" & Text.From([ApplicationID]) & "/Event=" & Text.From([EventID])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"EventIDUnique", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "OccurredTimeHourMinute", each #time(Time.Hour([Occurred]), Time.Minute([Occurred]), 0)),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"OccurredTimeHourMinute", type time}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type4", "OccurredTimeHour", each #time(Time.Hour([Occurred]), 0, 0)),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom4",{{"OccurredTimeHour", type time}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type5", "OccurredDateOnly", each DateTime.Date([Occurred]), type date)
in
#"Inserted Date"
Note that #"Changed Type" = Table.TransformColumnTypes line is not including "optional culture as nullable text" parameter.
https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumntypes states that that parameter is:
optional culture
text value corresponding to the culture values supported on your version of Windows, such as "en-US". If the culture is not specified, the current user culture is used.
When I import the CSVs generated in "en-AU" locale into PowerBI report on machine that is set to use "en-US" locale, date parsing fails, as I suppose is expected. I can correct it by setting File\Options and Settings\Options\Current File\Regional Settings to appropriate locate (in this case, English (Australia), and rerunning the queries. I can also correct it by hand by specifying "en-us" in Table.TransformColumnTypes parameter. This all I understand.
Now, when I import CSVs generated in "en-AU" locate into PowerBI report on machine that is set to use "en-AU" locale, date parsing still fails. To test this:
I can correct this by changing the current file File\Options and Settings\Options\Current File\Regional Settings again, but that is not what I want.
The settings for File\Options and Settings\Options\Global\Regional Settings\Model Language are set to "Use Application Language". The PBIT was prepared on en-US locale. It appears that that setting forces itself from my pbit template into generated pbix file that is generated, and PowerBI can't parse dates in non en-US formats without changing the Current File settings. Upon reading https://docs.microsoft.com/en-us/power-bi/supported-languages-countries-regions#choose-the-language-...
I am trying to reconcile the PowerBI setting with what Table.TransformColumnTypes states about the culture, "If the culture is not specified, the current user culture is used.". My OS user culture to en-AU, so I'd expect Table.TransformColumnTypes to work, but PowerBI setting for Region forces it back to en-US.
What can I do to have the PowerBI respect the OS culture?
PowerBI Versions.
Release:
November 2018
Product Version:
2.64.5285.741 (18.11) (x64)
OS Version:
Microsoft Windows NT 10.0.17134.0 (x64 en-US)
