vid 1336 -- ERROR in Date column

[krrishkrsna]

Hi


i am using Excel 2013 with Jan 2018 power query version


i was practicing 1336 Excel Magic Trick 1336_ Power Query_ Import Big Data Text Files_ Connection Only or Data Model_.mp4


found the ERROR "we could not parse the input provided as a date value" in Date column


from 1/13/2014 to 1/31/2014.
from 01/02/2014 to 12/02/2014 is in dd/mm/yyyy format but from 2/13/2014 again is in mm/dd/yyyy format


sorry i could not go further down to check errors as taking huge time to move down 'coz of huge number of rows


Changed the locale to en-Us, en-IN (IN-India) but still error


in pivot table placed Date (Row), Web Site (Column) and sum of Quantity, getting correct grand total as shown in vid but dates appearing in pivot table from mm/01/yyyy to mm/12/yyyy only


please give me the solution to correct the errors in Date column

thanks
Krishna

 

[macfuller]

Ideally you can correct the data source to give you consistent formatting. Failing that -

Assuming the dates are consistent in their formats for all future downloads (e.g. Feb 1 2014 - Feb 12 2014 is always dd/mm/yy and date and month are always 2 characters) I think you'll need to import the column as text and then parse out the portions of the string to convert the text back into dates with if...then statements. The question would be however how you could distinguish 02/03/2014 between Feb 3rd and March 2nd. Perhaps there's another field that would indicate the proper month, or maybe you can get the data source to give you a month name that would resolve the ambiguity?

If you have another field to resolve the ambiguity then you could add a line (depending on your locale) such as

if Table.AddColumn(Text.Range([DateText], 6, 4) = "2014" and
Text.Range([DateText], 4, 2) = "02" and
Number.FromText(Text.Range([DateText], 0, 2)) <= 12 and
Number.FromText(Text.Range([DateText], 0, 2)) >= 2 and
(other field logic to identify correct month)<other logic="" field="">
then Date.FromText("2014-02-" & Text.Range([DateText], 0, 2)
else Date.FromText([DateText]))

(rough estimate of the M code, not exact)</other>

 

[krrishkrsna]

thanks McFuller for your reply.

after importing and combining data into power query, i deleted the last step ChangeType in Query Steps and date column converted to Text format. then i changed date data type by using Locale. all dates are displaying correctly.