Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Cannot change data type to date

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, w...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
alutaaya_pbi
New Member
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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.