Good afternoon, I am a long time power user of Excel and have just broken into the Power everything realm.
I've read every post that I can find regarding date/time in Power Query (PQ) (by Ken and others) and tried everything I can think of to get this to work.
If I have missed something, please point me in the right direction.
I am attempting to import an XLS file downloaded from the client's website (legacy system generates a daily output file). In PQ, I delete the first 8 rows as it is primarily text and the column headers are scattered throughout the rows.
Column1 is a IndexId, Column2 is SubmitDate. In the actual xls file, the data in column 2 is formatted as yyyy-mm-dd, yet when I look at the data in column 2 in the formula bar, I can clearly see the data as mm/dd/yyyy hh:mm:ss am (or pm, as the case may be) and I need the data to be read into PQ in this format or as mm/dd/yyyy hh:mm:ss. Currently, PQ imports all the columns as Text. When I change column2 to be Date/Time, it reads the data as mm/dd/yyyy 12:00:00 am. Even if I try using "Use Local" and select English(US), I still get the data formatted as mm/dd/yyyy 12:00:00 am.
If I open the xls file using Excel, format column 2 as mm/dd/yyyy hh:mm:ss, close/save the file, and then import into PQ it works fine.
Is this one of those occasions where I need to use VBA to prep the files before bringing into PQ?
Any assistance would be greatly appreciated.
TcO
I've read every post that I can find regarding date/time in Power Query (PQ) (by Ken and others) and tried everything I can think of to get this to work.
If I have missed something, please point me in the right direction.
I am attempting to import an XLS file downloaded from the client's website (legacy system generates a daily output file). In PQ, I delete the first 8 rows as it is primarily text and the column headers are scattered throughout the rows.
Column1 is a IndexId, Column2 is SubmitDate. In the actual xls file, the data in column 2 is formatted as yyyy-mm-dd, yet when I look at the data in column 2 in the formula bar, I can clearly see the data as mm/dd/yyyy hh:mm:ss am (or pm, as the case may be) and I need the data to be read into PQ in this format or as mm/dd/yyyy hh:mm:ss. Currently, PQ imports all the columns as Text. When I change column2 to be Date/Time, it reads the data as mm/dd/yyyy 12:00:00 am. Even if I try using "Use Local" and select English(US), I still get the data formatted as mm/dd/yyyy 12:00:00 am.
If I open the xls file using Excel, format column 2 as mm/dd/yyyy hh:mm:ss, close/save the file, and then import into PQ it works fine.
Is this one of those occasions where I need to use VBA to prep the files before bringing into PQ?
Any assistance would be greatly appreciated.
TcO