Analytical dataflow Date format error

[Syndicate_Admin]

Good day everyone.

I have this really weird and annoying problem. 

I have set up analytical dataflow to pull data from an Oracle database through on premises gateway. This data has date.time columns that works just fine inside power query itself, up until I push the publish button.

The problem appears when I try to use this data anywhere else, for example inside Power BI report.

Clearly the problem is that Power Query stores the Date in US format, cause it returns Error for any date that has first value higher than 12. And there is no option to solve this by changing the date format. By the way this error is present only when using Power Apps Analytical Data Flow with the Microsoft Dataverse as a storage.

When using Azure Data Lake v2 as a storage, or setting the flow directly from Power BI Dataflows, the format is presumed perfectly.

I know that there is workarounds like changing the column type to text, or number and then back to date, but this is not an option cause this way I loose the incremental refresh option. 

I tried to change Regional Format inside Dataverse Settings to European, but this didn't help.

Also tried to force EU format in the initial query setup, no effect either 

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

It may be related to the different regions of your Power BI/ your database/your local region setting...

You may try:

1. Change the locale setting of the machine.

2. Go to File-->Options-->Current File -->Regional Settings--> try "Locale for Import" to change location.

3. Go to Power Query-->Right-click the date column--> Change Type --> Using Locale, in the Change Type with Locale dialog box, select  Date type and choose US or UK( US: mm/dd, UK : dd/mm)

For more information, please refer to:

Set a locale or region for data (Power Query)

Change Type Using Locale with Power Query

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SqlBobScot]

I have the same issue.  The solution suggested does not work. Any other suggestions?