cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tuffy Regular Visitor
Regular Visitor

Seeking advice on date column transformation

Hi,

 

I have a CSV file with one of the column "Transdate" and it has more than 2 million rows (25 columns).

 

Problem:

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. 

 

Question

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.

 

Thanks.

Tuff

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Seeking advice on date column transformation

I was able to tansform your sample without issue. Not sure if the large file is different in some way.

 

https://www.dropbox.com/s/agrpxnp9ys2zlwd/dates_edit.wmv?dl=1 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: Seeking advice on date column transformation

Did you restart power BI when prompted?


* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
16 REPLIES 16
Super User
Super User

Re: Seeking advice on date column transformation

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Tuffy Regular Visitor
Regular Visitor

Re: Seeking advice on date column transformation

Matt,

 

I deleted all the Change Data Type steps and the "TransDate" field back to its original data format(i.e. TEXT).pic1.JPG

 

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.pic2.JPG

 

Hence, I tried to convert a date in text format to date and it seems not working. 

Am I missing something here? 

 

Tuff

 

 

Super User
Super User

Re: Seeking advice on date column transformation

Did you try to parse the dates?  Transform\Date\Parse



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Tuffy Regular Visitor
Regular Visitor

Re: Seeking advice on date column transformation

Yes, I did,,, still errors on those records. 

Before the parse....


pic3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

after the parse...

pic4.JPG

 

Thanks.

Super User
Super User

Re: Seeking advice on date column transformation

OK.  I could take a look if you post a sample.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Tuffy Regular Visitor
Regular Visitor

Re: Seeking advice on date column transformation

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.

 

 

Re: Seeking advice on date column transformation

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

 

dates.jpgdates2.jpg

Super User
Super User

Re: Seeking advice on date column transformation

place it on dropbox or onedrive, and post a link



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Tuffy Regular Visitor
Regular Visitor

Re: Seeking advice on date column transformation