cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smjzahid
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
CNENFRNL
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
CNENFRNL
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors