Dataflows date and import issues

[jcullum]

Hi

Is anyone else having problems with dates in the dataflows?   I am in Australia and it is very frustrating trying to deal with dates.

Below is an example of a Date field, it is being displayed in the American format of MM/DD/YYYY, being an Aussie I would like it to be DD/MM/YYYY (to avoid confusion).  When i use that data it does come into Desktop in the correct format (see below for other issues however).  Minor issue i suppose.

It is annoying that it doesn't detect the date format on load.  I have to transform every column type (is this a feature or am i missing something).  I mean i do have Locale set to Australia and I am asking it to automatically detect column types.

However, the big issue I have, despite transforming it to a "Date" field on save it gets converted to a "DateTime" field in the Entity. When used in the desktop it comes in as DateTime.  That is going to seriously hamper the self service function if users need to convert it back.

In american format:

Locale & Auto detect columns

Field stored in the Entity as a Date Time.

The last issue i have relates to SharePoint Files imports.  I have a sharepoint site  that i use to get some excel files.  Using the Sharepoint Folder Connector seems to work and brings back a list of files however applying a Filter results in a Server error (500).  It does work however if you manually include the API version in the SharePoint.Files function via the advanced editor.  

For example:

Not Working: 

Source  = SharePoint.Files("https://somesite.sharepoint.com/sites/MySite"),

#"Filtered Rows" = Table.SelectRows(Source, each [Name] = "MyExcel.xlsx"),

Working:

Source  = SharePoint.Files("https://somesite.sharepoint.com/sites/MySite", [ApiVersion = 15]),

#"Filtered Rows" = Table.SelectRows(Source, each [Name] = "MyExcel.xlsx"),

Microsoft - You may what to get your Devs to fix the Get data web UI to include the ApiVersion to prevent the server throwing 500 errors.  I can work around it but it is not very intuitive.  

[v-shex-msft]

Hi @jcullum ,

Locale property is used to help power bi to recognize record values that diff from your device region format, it does not mean the display format. (e.g your date value stored as 'UK' date format but your system region is 'US', you can modify locale to 'UK' to help recognize 'UK' date values)

For data column type recognize issues, you can enter to query editor and manually fixed this in 'change type' step.

In fact, this optional parameter will include when you use on desktop side. (it will auto-generated based on the corresponding connector) It seems like power bi dev team forgot to add these parts on the dataflow side. (you can contact them and report this issue)

Regards,

Xiaoxin Sheng

[jcullum]

Hi Xiaoxin,

So i managed to fix the dd/mm/yyyy display issue. In-case someone else comes across the same problem,  it was a language setting in Chrome, I needed to specifically add the English (Australia) language and make sure it was at the top of the list and set to "This language is used to display the Google Chrome UI".  Interestingly i could not get Edge to work either.

The other problems still exist, i am just waiting for some in my organisation to assist with ticket.

Thanks for your help on the display issue.

[pat_energetics]

Same issue now for two months.

MS team are aware that non-US dates do not load correctly in dataflows, but as of today's advice have kicked the "fix" back to February.

Where the data source comes from SharePoint, even more issues are created due to the US date format default - but even explicitly setting the date to AU region will not resolve the error.

Latest suggested resolution was to leave all date fields as text....

[jcullum]

Thanks for the the reply. 

A Couple of things.  I don't know what you mean by my region being US.  As far as I know my region is AU, not US, my regional settings on my pc are australian.  The dataflows are in the is SouthEast Australia Azure cloud.  

With respect data column, I did state that i am already changing the type, the bigger concern is i am setting it to Date and it is being stored in the entity as DateTime. 

Unfortunately i tired to submit a ticket but it didn't work, i need my organisation to provide me with access.  I will be following it up today.