topic Expression Error (Cannot convert the value of type date to text) in Power Query
https://community.powerbi.com/t5/Power-Query/Expression-Error-Cannot-convert-the-value-of-type-date-to-text/m-p/1586377#M48968
<P>I have a and excel file with TEXT columns and date values have been stored in those columns. for eg:</P><P> </P><P>Few of the cells have been formatted as type General (until 14/12/20) </P><P>and anything after 14/12 is formatted as Customised. </P><P> </P><P>Power Query throws error fro this cell onwards (15/12) meaning from where the format is set to customised.</P><P> </P><P>How do I resolve this error.</P><P> </P><P>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.</P><P> </P><P>Please find the sample data below</P><P> </P><TABLE><TBODY><TR><TD>02-Dec-20 A</TD><TD>02-Dec-20 A</TD><TD>03-Dec-20 A</TD><TD>03-Dec-20 A</TD><TD>07-Dec-20 A</TD><TD>08-Dec-20 A</TD><TD>08-Dec-20 A</TD><TD>09-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>11-Dec-20 A</TD><TD>11-Dec-20 A</TD><TD>11-Dec-20 A</TD><TD>14-Dec-20*</TD><TD>14-Dec-20*</TD><TD>15-Dec-20</TD><TD>15-Dec-20</TD><TD>16-Dec-20</TD><TD>16-Dec-20</TD><TD>17-Dec-20</TD><TD>17-Dec-20</TD><TD>18-Dec-20</TD><TD>18-Dec-20</TD><TD>23-Dec-20 A</TD><TD>05-Jan-21</TD><TD>07-Jan-21</TD><TD>07-Jan-21</TD><TD>13-Jan-21</TD><TD>13-Jan-21</TD><TD>13-Jan-21</TD><TD>13-Jan-21</TD><TD>14-Jan-21</TD><TD>15-Jan-21</TD><TD>15-Jan-21</TD><TD>15-Jan-21</TD><TD>15-Jan-21</TD><TD>25-Jan-21</TD><TD>25-Jan-21</TD><TD>25-Jan-21</TD><TD>25-Jan-21</TD><TD>26-Jan-21</TD><TD>26-Jan-21</TD><TD>26-Jan-21</TD><TD>26-Jan-21</TD><TD>28-Jan-21</TD><TD>28-Jan-21</TD><TD>28-Jan-21</TD><TD>29-Jan-21</TD><TD>29-Jan-21</TD><TD>29-Jan-21</TD><TD>02-Feb-21</TD><TD>03-Feb-21</TD><TD>03-Feb-21</TD><TD>03-Feb-21</TD><TD>03-Feb-21</TD><TD>05-Feb-21</TD><TD>09-Feb-21</TD><TD>10-Feb-21</TD><TD>12-Feb-21</TD><TD>12-Feb-21</TD><TD>16-Feb-21</TD><TD>17-Feb-21</TD><TD>17-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>23-Feb-21</TD><TD>23-Feb-21</TD><TD>24-Feb-21</TD><TD>24-Feb-21</TD><TD>26-Feb-21</TD><TD>26-Feb-21</TD><TD>01-Mar-21</TD><TD>01-Mar-21</TD><TD>01-Mar-21</TD><TD>02-Mar-21</TD><TD>03-Mar-21</TD><TD>03-Mar-21</TD><TD>05-Mar-21</TD><TD>08-Mar-21</TD><TD>08-Mar-21</TD><TD>08-Mar-21</TD><TD>08-Mar-21</TD></TR></TBODY></TABLE><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 681px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/428464i866E42360ABB2BE2/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>Thu, 07 Jan 2021 14:03:29 GMTsmjzahid2021-01-07T14:03:29ZExpression Error (Cannot convert the value of type date to text)
https://community.powerbi.com/t5/Power-Query/Expression-Error-Cannot-convert-the-value-of-type-date-to-text/m-p/1586377#M48968
<P>I have a and excel file with TEXT columns and date values have been stored in those columns. for eg:</P><P> </P><P>Few of the cells have been formatted as type General (until 14/12/20) </P><P>and anything after 14/12 is formatted as Customised. </P><P> </P><P>Power Query throws error fro this cell onwards (15/12) meaning from where the format is set to customised.</P><P> </P><P>How do I resolve this error.</P><P> </P><P>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.</P><P> </P><P>Please find the sample data below</P><P> </P><TABLE><TBODY><TR><TD>02-Dec-20 A</TD><TD>02-Dec-20 A</TD><TD>03-Dec-20 A</TD><TD>03-Dec-20 A</TD><TD>07-Dec-20 A</TD><TD>08-Dec-20 A</TD><TD>08-Dec-20 A</TD><TD>09-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>10-Dec-20 A</TD><TD>11-Dec-20 A</TD><TD>11-Dec-20 A</TD><TD>11-Dec-20 A</TD><TD>14-Dec-20*</TD><TD>14-Dec-20*</TD><TD>15-Dec-20</TD><TD>15-Dec-20</TD><TD>16-Dec-20</TD><TD>16-Dec-20</TD><TD>17-Dec-20</TD><TD>17-Dec-20</TD><TD>18-Dec-20</TD><TD>18-Dec-20</TD><TD>23-Dec-20 A</TD><TD>05-Jan-21</TD><TD>07-Jan-21</TD><TD>07-Jan-21</TD><TD>13-Jan-21</TD><TD>13-Jan-21</TD><TD>13-Jan-21</TD><TD>13-Jan-21</TD><TD>14-Jan-21</TD><TD>15-Jan-21</TD><TD>15-Jan-21</TD><TD>15-Jan-21</TD><TD>15-Jan-21</TD><TD>25-Jan-21</TD><TD>25-Jan-21</TD><TD>25-Jan-21</TD><TD>25-Jan-21</TD><TD>26-Jan-21</TD><TD>26-Jan-21</TD><TD>26-Jan-21</TD><TD>26-Jan-21</TD><TD>28-Jan-21</TD><TD>28-Jan-21</TD><TD>28-Jan-21</TD><TD>29-Jan-21</TD><TD>29-Jan-21</TD><TD>29-Jan-21</TD><TD>02-Feb-21</TD><TD>03-Feb-21</TD><TD>03-Feb-21</TD><TD>03-Feb-21</TD><TD>03-Feb-21</TD><TD>05-Feb-21</TD><TD>09-Feb-21</TD><TD>10-Feb-21</TD><TD>12-Feb-21</TD><TD>12-Feb-21</TD><TD>16-Feb-21</TD><TD>17-Feb-21</TD><TD>17-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>22-Feb-21</TD><TD>23-Feb-21</TD><TD>23-Feb-21</TD><TD>24-Feb-21</TD><TD>24-Feb-21</TD><TD>26-Feb-21</TD><TD>26-Feb-21</TD><TD>01-Mar-21</TD><TD>01-Mar-21</TD><TD>01-Mar-21</TD><TD>02-Mar-21</TD><TD>03-Mar-21</TD><TD>03-Mar-21</TD><TD>05-Mar-21</TD><TD>08-Mar-21</TD><TD>08-Mar-21</TD><TD>08-Mar-21</TD><TD>08-Mar-21</TD></TR></TBODY></TABLE><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 681px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/428464i866E42360ABB2BE2/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>Thu, 07 Jan 2021 14:03:29 GMThttps://community.powerbi.com/t5/Power-Query/Expression-Error-Cannot-convert-the-value-of-type-date-to-text/m-p/1586377#M48968smjzahid2021-01-07T14:03:29ZRe: Expression Error (Cannot convert the value of type date to text)
https://community.powerbi.com/t5/Power-Query/Expression-Error-Cannot-convert-the-value-of-type-date-to-text/m-p/1586502#M48975
<P><LI-USER uid="109184"></LI-USER> , you may want to try</P><LI-CODE lang="markup">Table.SelectRows(#"Added Index", each Date.From([Finish])>#date(2020,12,14))</LI-CODE>Thu, 07 Jan 2021 15:33:48 GMThttps://community.powerbi.com/t5/Power-Query/Expression-Error-Cannot-convert-the-value-of-type-date-to-text/m-p/1586502#M48975CNENFRNL2021-01-07T15:33:48Z