Help - Date column syntax changes from raw csv data when importing through powerquery

%3CLINGO-SUB%20id%3D%22lingo-sub-2329007%22%20slang%3D%22en-US%22%3EHelp%20-%20Date%20column%20syntax%20changes%20from%20raw%20csv%20data%20when%20importing%20through%20powerquery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2329007%22%20slang%3D%22en-US%22%3E%3CP%3EI%20desperately%20need%20assistance%20for%20a%20data%20import%20project%20I%20am%20working%20on.%20Our%20developer%20converted%20a%20large%20data%20set%20from%20SQL%20to%20csv.%20The%20date%20column%20is%20showing%20in%20this%20syntax%26nbsp%3B12%3A18%3A37%20AM%20when%20it%20is%20imported%20through%20powerquery%20in%20excel.%20I%20have%20tried%20to%20change%20the%20type%20and%20it%20gives%20me%20an%20error.%20I%20need%20the%20actual%20date%20format%20for%20this%20import.%20Any%20help%20is%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2329007%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2329712%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20Date%20column%20syntax%20changes%20from%20raw%20csv%20data%20when%20importing%20through%20powerquery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2329712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1046289%22%20target%3D%22_blank%22%3E%40jmccardle77%3C%2FA%3E%26nbsp%3BWould%20be%20good%20if%20you%20could%20show%20how%20the%20%22date%22%20looks%20like%20in%20the%20CSV%20file%20and%20what%20date%20you%20expect%20the%20get%20from%20PQ.%20Could%20it%20be%20that%20December%2018%2C%201937%20is%20represented%20in%20the%20CSV%20as%2012%3A18%3A37%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20remove%20the%20automatic%20data%20type%20detection%20step%20so%20that%20you%20keep%20the%20%22raw%22%20data%20as%20it%20is%20in%20the%20CSV.%20In%20the%20date%20column%2C%20replace%20the%20colons%20by%20%22%2F%22%20or%20%22-%22%20and%20then%20change%20the%20data%20type%20to%20Date.%20Perhaps%20you%20need%20to%20do%20this%20using%20a%20%22Date-locale%20for%20the%20US%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I desperately need assistance for a data import project I am working on. Our developer converted a large data set from SQL to csv. The date column is showing in this syntax 12:18:37 AM when it is imported through powerquery in excel. I have tried to change the type and it gives me an error. I need the actual date format for this import. Any help is appreciated. 

1 Reply

@jmccardle77 Would be good if you could show how the "date" looks like in the CSV file and what date you expect the get from PQ. Could it be that December 18, 1937 is represented in the CSV as 12:18:37?

 

If so, remove the automatic data type detection step so that you keep the "raw" data as it is in the CSV. In the date column, replace the colons by "/" or "-" and then change the data type to Date. Perhaps you need to do this using a "Date-locale for the US".