Cannot change data type to date

[Anonymous]

NAV System one table have two date column: shipment date and posting date , I can change shipment date to date type, cannot change posting date to date type, I need date hierarchy for posting date, why same table, posting date column cannot change data type to date? Is anyone have same issue ? Thank you

[Anonymous · Accepted solution]

Please post some sample data for us to assist.  The answer to you question will be hidden in the data you are working with.

[alutaaya_pbi]

Mine changes half the data type to date, whilst the other half of the rows display the word "error" where the "date" should have been displayed. The Power Query Editor in Power BI Desktop seems to have a bug!

The same data source does work fine in Office 2016 Professional Plus - when using the Power Query Editor. Power Pivot features work well in Excel. It is possible that the Power BI Desktop / Power Query Editor - might have an issue. 

[Anonymous]

Ok, lets start with some basics then.  How is your date stored in your source dataset?  Are they stored as strings, or as numbers in the 'Days since 31/12/1899' format?

Check for a pattern on what dates were changed fine and what dates failed.  For example, did dates where the "Day" component was 12 or less convert correctly, but dates after the 13th fail?  If so, your dates are being classified as the opposite format to what you are expecting (i.e. DD/MM/YYYY vs MM/DD/YYYY).

Are all if your dates just dates, or as some of them DateTimes?

One option is to format your dates as in the source, or part of the transformation YYYY-MM-DD.

[Anonymous]

my problem fixed by this solution, https://community.powerbi.com/t5/Desktop/Date-Hierarchy-falls-to-appear-when-joining-on-the-date/td-... , thank you for help