Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Did you restart power BI when prompted?


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

16 REPLIES 16

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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

 

 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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

Before the parse....


pic3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

after the parse...

pic4.JPG

 

Thanks.

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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

 

dates.jpgdates2.jpg

place it on dropbox or onedrive, and post a link



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Matt,

 

I am still having error and therefore, I swtich to traditional way by adding three custom columns and breakdown the Transdate into YYYY, MM, DD and later combine them into Date (text) and finally convert it into the proper date format by using Date.FromText function, and it works.

 

I means I can transfer the data in excel but I just want to perform this kinds of activities directly in Query Editor in case the file is too large.

 

I will flag your last feedback as acceped solution and if you don't mind let me now how to pull out those two checkboxes that will be great.

Anonymous
Not applicable

Matt,

 

That is so strange why you don't get any error while performing type changes using Locale. I will figure out.

Between, may I know how you enable the two checkboxes (i.e. Column Distribution and Column Quality) as it is cool stuff to find out status of the column. I can't find it Data View pane. Thanks.

 

pic6.JPG

sorry, I was going to mention how to turn it on, but I was running late for a training course.  Go to Options and Settings\Options\Preview Features and turn on column profiling. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

I did the enablement in the option setting but the two checkboxes do not display.

Did you restart power BI when prompted?


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Matt. case closed Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.