Forum Discussion
smjzahid
Helper V
Helper VExpression Error (Cannot convert the value of type date to text)
I have a and excel file with TEXT columns and date values have been stored in those columns. for eg:
Few of the cells have been formatted as type General (until 14/12/20)
and anything after 14/12 is formatted as Customised.
Power Query throws error fro this cell onwards (15/12) meaning from where the format is set to customised.
How do I resolve this error.
Note: This file is quite huge and I need to find a permanent solution via Power Query to handle this as every week the data will come out of the source system in this format only.
Please find the sample data below
| 02-Dec-20 A | 02-Dec-20 A | 03-Dec-20 A | 03-Dec-20 A | 07-Dec-20 A | 08-Dec-20 A | 08-Dec-20 A | 09-Dec-20 A | 10-Dec-20 A | 10-Dec-20 A | 10-Dec-20 A | 10-Dec-20 A | 11-Dec-20 A | 11-Dec-20 A | 11-Dec-20 A | 14-Dec-20* | 14-Dec-20* | 15-Dec-20 | 15-Dec-20 | 16-Dec-20 | 16-Dec-20 | 17-Dec-20 | 17-Dec-20 | 18-Dec-20 | 18-Dec-20 | 23-Dec-20 A | 05-Jan-21 | 07-Jan-21 | 07-Jan-21 | 13-Jan-21 | 13-Jan-21 | 13-Jan-21 | 13-Jan-21 | 14-Jan-21 | 15-Jan-21 | 15-Jan-21 | 15-Jan-21 | 15-Jan-21 | 25-Jan-21 | 25-Jan-21 | 25-Jan-21 | 25-Jan-21 | 26-Jan-21 | 26-Jan-21 | 26-Jan-21 | 26-Jan-21 | 28-Jan-21 | 28-Jan-21 | 28-Jan-21 | 29-Jan-21 | 29-Jan-21 | 29-Jan-21 | 02-Feb-21 | 03-Feb-21 | 03-Feb-21 | 03-Feb-21 | 03-Feb-21 | 05-Feb-21 | 09-Feb-21 | 10-Feb-21 | 12-Feb-21 | 12-Feb-21 | 16-Feb-21 | 17-Feb-21 | 17-Feb-21 | 22-Feb-21 | 22-Feb-21 | 22-Feb-21 | 22-Feb-21 | 22-Feb-21 | 22-Feb-21 | 23-Feb-21 | 23-Feb-21 | 24-Feb-21 | 24-Feb-21 | 26-Feb-21 | 26-Feb-21 | 01-Mar-21 | 01-Mar-21 | 01-Mar-21 | 02-Mar-21 | 03-Mar-21 | 03-Mar-21 | 05-Mar-21 | 08-Mar-21 | 08-Mar-21 | 08-Mar-21 | 08-Mar-21 |
