"Changed Type to Locale" either returning error or...

Ski900 · ‎04-10-2018

In the API call is use all DateTime stamps are received in the format below (here I use created_at as the example). I am based out of Seattle and am building a dashboard for clients who use "dd/mm/yyyy" format. The issue I'm having has a few different outcomes, so I will list them all in hopes it helps nail down the issue I'm having.

Outcome 1: When I pull in the data, I change type to DateTimeZone so it maintains the UTC time designation and does not automatically convert to PST. Then I "Change Type to Locale" with "Data Type = Date/Time/Timezone" and "Locale = English (Australia)". Using this method all dates are converted to "mm/dd/yyyy" format, so the Locale option has no effect.

Outcome 2: I convert to DateTimeZone, set "Change Type to Locale" with "Data Type = Text" and "Locale = English (Australia)". With "Text" being select it returns all values in the column into exactly what I'm looking for; "dd/mm/yyyy" format. Then I "Change Type" to DateTimeZone and I get "DataFormat.Error: We couldn't parse the input provided as a DateTimeZone value" for approx. 95% of the rows.

Outcome 3: In desktop if I go to File > Options and Settings > Options > Regional Settings, set "Locale for import" to English (Australia), and completed the steps listed in the previous outcomes, I do not get the error in Outcome 2, but everything just returns to the format "mm/dd/yyyy".

I have also adjusted my Country or Region Windows 10 setting to Australia, but that didn't do anything. I'm totally lost; it feels as if this should be much easier than I'm making it out to be. Any help is highly appreciated.

created_at

2017-12-15T19:50:32Z

2017-12-15T19:50:33Z

2018-01-04T20:11:59Z

2018-01-04T20:17:14Z

2018-01-23T02:47:29Z

2018-01-23T02:53:01Z

2018-01-23T02:54:47Z

2018-01-23T03:18:31Z

2018-01-23T04:02:15Z

2018-01-23T06:33:15Z

2018-01-24T01:14:32Z

2018-01-24T01:31:38Z

2018-01-24T02:07:11Z

2018-01-24T07:29:01Z

2018-01-24T09:49:54Z

2018-01-24T09:53:02Z

2018-01-24T22:33:25Z

2018-01-24T22:49:41Z

2018-01-25T00:17:45Z

2018-02-13T13:06:19Z

2018-02-13T16:35:30Z

2018-02-13T16:41:09Z

2018-02-13T16:46:41Z

2018-02-13T16:46:51Z

2018-02-14T04:40:40Z

2018-02-14T07:15:54Z

2018-02-14T08:21:26Z

2018-02-14T08:23:00Z

2018-02-14T08:53:37Z

2018-02-14T11:38:46Z

2018-02-14T11:42:36Z

2018-02-14T11:43:35Z

v-jiascu-msft · ‎04-12-2018

Hi @Ski900,

The "locale for import" option indicates the format of the source files. Power BI will recognize the files format and transfer them into the formats of the OS where Power BI locates. In you scenario, I would suggest you change the Power BI settings to "English (United States)". The OS format should be "English (Australia)".

Best Regards,

Dale

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

Ski900 · ‎04-13-2018

Hi Dale, you seem to be my saving grace as of recently :). Just to confirm, I should set my Power BI region to English (United States) and set my Windows 10 region to English (Australia)? Also, with the example you show in the picture, what format are those dates in, actual date format or text? Thanks!