Sorting in Dataflow

[avidthinker]

Hi,

I am having an issue with a DateTime column I converted to Date to sort to get rid of duplicates in another column.

DataFormat.Error: We couldn't parse the input provided as a DateTime value.

Details

Reason = DataFormat.Error

Detail = 0000-00-00 00:00:00

Prior to 16 Feb this was working in Power BI Fabric Service.

Previously Dates displayed as dd/mm/yyyy or mm/dd/yyyy was working not sure why the date needs to be in yyyy-mm-dd format?

Tried convert using locale, convert from DateTime to Date then sort but same issue.

MCode

Table.Sort(#"Changed column type 1", {{"Order xxx", Order.Descending}})

[avidthinker]

Hi lbendlin,

Thanks for the update, reason being in sorting against a date column, I have duplicates in my main key column I would like to remove duplicates so I do not get issues with my 1:n relationships downstream.

I guess if I am using the file to populate the dataflow I will need to cleanse my data before utilising it, but this functionality was working without issue prior to Fabric Feb update, wonder what MS had done to now cause sorts to fail if not formatted correctly.

[lbendlin]

Thanks for the update, reason being in sorting against a date column, I have duplicates in my main key column I would like to remove duplicates so I do not get issues with my 1:n relationships downstream.

This still does not require sorting.  Do a Table.Distinct on your intended key column and Power Query will randomly pick the row to keep.

[lbendlin]

ISO8601 format is generally preferred to reduce ambiguity.

Sorting in Power Query must be avoided as it is an extremely costly operation, and usually not required in the downstream process.