Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
DataFormat.Error: We couldn't parse the input provided as a DateTime value. Details: 14/11/2018 7:00:00 AM
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?
HI @Anonymous,
Power query date format is based on your system region setting, you can't manually modify it and keep date type in power query side.(after convert date value to particular format, it will transform to text type)
Current it support to use locale to format specific text value to date type, but you can't convert to date type and keep that format at same time.
Regards,
Xiaoxin Sheng
Hello there,
>>Power query date format is based on your system region setting
That is exactly what I am asking. If PowerQuery supposed to respect my system's regional setting, it is not doing so.
It appears that the File\Options and Settings\Options\Current File\Regional Settings setting is forcing date evaluation according to the locale of the system where I authored the template pbit, which was driven by File\Options and Settings\Options\Global\Regional Settings\Model Language and was set to en-US.
How can I have PowerBI respect my system's regional setting?
Daniel
Hi @Anonymous,
You can change it in system control panel-> clock and region -> change date, time or number formats option:
Open additional setting and modify date format and re-launch power bi, you can found report date format has been changed.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |