Region date query problems - Desktop vs. Dataflow - Cannot convert value #date to type Text

[Veles]

Hi,

Trying to turn a query I have on desktop into a dataflow. The main issue I am having now is that my date formats are dd/mm/yyyy which on my desktop query doesn't cause any problems. When I recreate the query in a dataflow I get a conflict of the data formats (it looks like dataflow is ignoring our region setting).

To get around this I'm trying to change the query step so instead of changing the column type to date I'm trying to put in a custom column using the #date function. I'm hoping this will get around the region issues.

= Table.AddColumn(#"Added Prefix", "Order Date1", each 
#date( 
    Number.FromText(
        Text.End([Order Date],4)
    ),
    Number.FromText(
        Text.Middle([Order Date],4,2)
    ),
    Number.FromText(
        Text.Start([Order Date],2)
    )
),type date)

However, I'm getting the below error

Not sure why this is happening as I'm not trying to convert to text 

[Anonymous]

What you need to do is use the change type with locale, this can be done when you right click on the column go to the change type drop down and click with locale.

I do not know of a way to do locale type changes using the "type date" synatx.

[Veles]

I had never spotted that before! Brilliant stuff I'll give that a go.