I have a CSV file with one of the column "Transdate" and it has more than 2 million rows (25 columns).
I notice that some of the value in the "Transdate" column has a data format of Date, but other cell value in the "Transdate" column has a text value (i.e. GENERAL in excel) when I open it in excel.
Hence, I load the file (Get Data) from the Power BI Desktop and I open up the power query editor and make data type changes on the "Transdate" column (i.e. Right-click TranDate → Change Type → Using Locale → Date → English (Australia)).
Next, I Close & Apply.
Power BI response with error on the "Transdate" column and I notice the "Error" cell fall in this column are those with Text value in the original format.
As the original value of those error cell in "Transdate" have valid value even though they had been formatted as text format, I cannot remove them by performing "Reduce Row"-"Remove Error" as it will reduce the rows and I will loss important data records.
How to solve this issue by using Power BI query editor without open up the file in Excel to perform the transformation (i.e. changing the data type of the whole colume to Date format) beside excel cannot open more than 2 millions rows.
Solved! Go to Solution.
I was able to tansform your sample without issue. Not sure if the large file is different in some way.
So are the rogue dates in quotes in the csv? One thing you could try is to delete all change data type steps, the. Set the data type to text. Then use date/parse to convert the text to a date (don’t replace the previous data type change, add another.
I deleted all the Change Data Type steps and the "TransDate" field back to its original data format(i.e. TEXT).
Next I tried to following methods and there is still errors:
1 - change locale of the date format (right click transdate - change type - using locale - date - English (australia))
2 - direct changing the data type to date (right click transdate - change type - date)
Note - 1st method is the prefer method as it help convert all US date format to country specific format based on system setting.
Yet... error of records still manifested.
Hence, I tried to convert a date in text format to date and it seems not working.
Am I missing something here?
Thanks for your help but may I know if there is anyway to send you the file (a small size) if I understand you correctly as I can't post a sample file in the forum.
You could try using the example tool Query Editor > Add Column > Column From Examples.
Below I loaded as set of dates as text formated d/m/yyyy then used the eample to tell it how to get ot m/d/yyyy
The file (Jan 2008.csv):