Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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,
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?
Tuff
Did you try to parse the dates? Transform\Date\Parse
Yes, I did,,, still errors on those records.
Before the parse....
after the parse...
Thanks.
OK. I could take a look if you post a sample.
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
place it on dropbox or onedrive, and post a link
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,
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.
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.
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.
I did the enablement in the option setting but the two checkboxes do not display.
Matt. case closed
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
101 | |
74 | |
70 | |
47 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |