cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Expression 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 A02-Dec-20 A03-Dec-20 A03-Dec-20 A07-Dec-20 A08-Dec-20 A08-Dec-20 A09-Dec-20 A10-Dec-20 A10-Dec-20 A10-Dec-20 A10-Dec-20 A11-Dec-20 A11-Dec-20 A11-Dec-20 A14-Dec-20*14-Dec-20*15-Dec-2015-Dec-2016-Dec-2016-Dec-2017-Dec-2017-Dec-2018-Dec-2018-Dec-2023-Dec-20 A05-Jan-2107-Jan-2107-Jan-2113-Jan-2113-Jan-2113-Jan-2113-Jan-2114-Jan-2115-Jan-2115-Jan-2115-Jan-2115-Jan-2125-Jan-2125-Jan-2125-Jan-2125-Jan-2126-Jan-2126-Jan-2126-Jan-2126-Jan-2128-Jan-2128-Jan-2128-Jan-2129-Jan-2129-Jan-2129-Jan-2102-Feb-2103-Feb-2103-Feb-2103-Feb-2103-Feb-2105-Feb-2109-Feb-2110-Feb-2112-Feb-2112-Feb-2116-Feb-2117-Feb-2117-Feb-2122-Feb-2122-Feb-2122-Feb-2122-Feb-2122-Feb-2122-Feb-2123-Feb-2123-Feb-2124-Feb-2124-Feb-2126-Feb-2126-Feb-2101-Mar-2101-Mar-2101-Mar-2102-Mar-2103-Mar-2103-Mar-2105-Mar-2108-Mar-2108-Mar-2108-Mar-2108-Mar-21

 

image.png

1 ACCEPTED SOLUTION
Super User III
Super User III

@smjzahid , you may want to try

Table.SelectRows(#"Added Index", each Date.From([Finish])>#date(2020,12,14))

View solution in original post

1 REPLY 1
Super User III
Super User III

@smjzahid , you may want to try

Table.SelectRows(#"Added Index", each Date.From([Finish])>#date(2020,12,14))

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors