[Expression.Error] We cannot convert the value #date(2017, 4, 3) to Type Text

[danextian]

Hi  All,

I'm trying  to extract the first date from a column. A cell contains text like this:

1/11/17 communication
1/10 communication
1/9 communication
1/6 communication
1/5-communication
1/4 I emailed the following:
items a
items b

I was able to split them into column using the split column feature into rows by line feed. After splitting, i trimmed the new column then split them again by delimeter (blanks space) at the first occurence. So far so good.

But since some do not  separate the date value from text using a blank  space. I had to split again the already splitted column.  After splitting, I tried using filter and clicked "load more" to show all values. And  I got this error [Expression.Error] We cannot convert the value #date(2017, 4, 3) to type Text. I am not sure how to deal with it. 

[MarcelBeug]

Can you share your code and/or example data with expected results?

Your explanation is insufficient - at least for me - to recreate what you have done so far.

The following code works fine with me, so there must be something special:

= Text.From(#date(2017,4,3))

[danextian]

Hi,

Please see below:

let
Source = Folder.Files("C:\Users\username\Google Drive\ReportRaw"),
#"Filtered Rows3" = Table.SelectRows(Source, each Text.Contains([Extension], ".xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows3",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "ExcelFileLoad", each Excel.Workbook([Content])),
#"Expanded ExcelFileLoad" = Table.ExpandTableColumn(#"Added Custom", "ExcelFileLoad", {"Name", "Data", "Item", "Kind", "Hidden"}, {"ExcelFileLoad.Name", "ExcelFileLoad.Data", "ExcelFileLoad.Item", "ExcelFileLoad.Kind", "ExcelFileLoad.Hidden"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded ExcelFileLoad", each not Text.Contains([Name], "$") and not Text.Contains([Name], "(") and not Text.Contains([Name], "alendar")),
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows2", each ([ExcelFileLoad.Name] = "Tracker")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "PromoteHeaders", each Table.PromoteHeaders([ExcelFileLoad.Data])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ExcelFileLoad.Data", "ExcelFileLoad.Kind", "ExcelFileLoad.Hidden", "ExcelFileLoad.Item"}),
#"Expanded PromoteHeaders" = Table.ExpandTableColumn(#"Removed Columns", "PromoteHeaders", {"Case #", "Ticket Created", "Account Name", "Geo", "Country", "Case Type", "Queued On Date", "Priority", "Date Case Assigned", "Queued At", "CAM", "Month Reporting", "Count", "Current date", "Ticket Age", "Age Range", "Required SLA#(lf)(# of CPN)", "Actual Lead Time#(lf)", "Target Lead Time#(lf)(Good/Bad Requests)", "Actual vs Target Lead Time", "Achieved or Not Achieved (E2E LT)", "Total # of Days processing#(lf)(Actual minus redo dates)", "Total# of Days vs Target Lead Time #(lf)(V-Q)", "ACHIEVED or Not Achieved#(lf)(Internal Speed)", "STATUS", "Date Closed#(lf)*Should Be Final date", "Original Date Re-routed to other teams (case, ticket, or e-mail for missing docs/data)", "Date Redo back to CAM Analyst", "CRM Sales Contract", "Good Requests ?#(lf)(Y/N)#(lf)*Should NOT be blank", "# of CPNs created#(lf)*Should NOT be blank", "Shell CPN (Y/N)#(lf)*Should NOT be blank", "Shell CPN creation support", "Remarks#(lf)(Follow 5 Ws and How)", "Bad Request Delay Reason Category (Col. U)#(lf)*Closest or Main reason for the delay ", "Internal Speed - Good Request Delay Reason (Col. X)#(lf)*Should Not Be Blank ", "Specific Reason#(lf)*Specify or elaborate further reason relative to delay category", "Source of Delay #(lf)", "URGENT Request#(lf)(Y / N)#(lf)*Should NOT be blank", "Urgent Reason Category", "Explanation as to Reason for Urgency", "Review Report Generated - for Temp file#(lf)(Y / NA)#(lf)(Should not be Blank)", "Cluster Number", "Formula 1", "Formula 2", "Formula 3"}, {"Case #", "Ticket Created", "Account Name", "Geo", "Country", "Case Type", "Queued On Date", "Priority", "Date Case Assigned", "Queued At", "CAM", "Month Reporting", "Count", "Current date", "Ticket Age", "Age Range", "Required SLA#(lf)(# of CPN)", "Actual Lead Time#(lf)", "Target Lead Time#(lf)(Good/Bad Requests)", "Actual vs Target Lead Time", "Achieved or Not Achieved (E2E LT)", "Total # of Days processing#(lf)(Actual minus redo dates)", "Total# of Days vs Target Lead Time #(lf)(V-Q)", "ACHIEVED or Not Achieved#(lf)(Internal Speed)", "STATUS", "Date Closed#(lf)*Should Be Final date", "Original Date Re-routed to other teams (case, ticket, or e-mail for missing docs/data)", "Date Redo back to CAM Analyst", "CRM Sales Contract", "Good Requests ?#(lf)(Y/N)#(lf)*Should NOT be blank", "# of CPNs created#(lf)*Should NOT be blank", "Shell CPN (Y/N)#(lf)*Should NOT be blank", "Shell CPN creation support", "Remarks#(lf)(Follow 5 Ws and How)", "Bad Request Delay Reason Category (Col. U)#(lf)*Closest or Main reason for the delay ", "Internal Speed - Good Request Delay Reason (Col. X)#(lf)*Should Not Be Blank ", "Specific Reason#(lf)*Specify or elaborate further reason relative to delay category", "Source of Delay #(lf)", "URGENT Request#(lf)(Y / N)#(lf)*Should NOT be blank", "Urgent Reason Category", "Explanation as to Reason for Urgency", "Review Report Generated - for Temp file#(lf)(Y / NA)#(lf)(Should not be Blank)", "Cluster Number", "Formula 1", "Formula 2", "Formula 3"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded PromoteHeaders",{"Content"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"ExcelFileLoad.Name", "TabName"}, {"Name", "FileName"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [#"Case #"] <> null and [#"Case #"] <> ""),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows1", "Case #", "Case # - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column","Case # - Copy",Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false),{"Case # - Copy.1", "Case # - Copy.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Case # - Copy.1", type text}, {"Case # - Copy.2", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Case # - Copy.1", "Unique Case #"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"Case # - Copy.2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"FileName", "TabName", "Case #", "Unique Case #", "Ticket Created", "Account Name", "Geo", "Country", "Case Type", "Queued On Date", "Priority", "Date Case Assigned", "Queued At", "CAM", "Month Reporting", "Count", "Current date", "Ticket Age", "Age Range", "Required SLA#(lf)(# of CPN)", "Actual Lead Time#(lf)", "Target Lead Time#(lf)(Good/Bad Requests)", "Actual vs Target Lead Time", "Achieved or Not Achieved (E2E LT)", "Total # of Days processing#(lf)(Actual minus redo dates)", "Total# of Days vs Target Lead Time #(lf)(V-Q)", "ACHIEVED or Not Achieved#(lf)(Internal Speed)", "STATUS", "Date Closed#(lf)*Should Be Final date", "Original Date Re-routed to other teams (case, ticket, or e-mail for missing docs/data)", "Date Redo back to CAM Analyst", "CRM Sales Contract", "Good Requests ?#(lf)(Y/N)#(lf)*Should NOT be blank", "# of CPNs created#(lf)*Should NOT be blank", "Shell CPN (Y/N)#(lf)*Should NOT be blank", "Shell CPN creation support", "Remarks#(lf)(Follow 5 Ws and How)", "Bad Request Delay Reason Category (Col. U)#(lf)*Closest or Main reason for the delay ", "Internal Speed - Good Request Delay Reason (Col. X)#(lf)*Should Not Be Blank ", "Specific Reason#(lf)*Specify or elaborate further reason relative to delay category", "Source of Delay #(lf)", "URGENT Request#(lf)(Y / N)#(lf)*Should NOT be blank", "Urgent Reason Category", "Explanation as to Reason for Urgency", "Review Report Generated - for Temp file#(lf)(Y / NA)#(lf)(Should not be Blank)", "Cluster Number", "Formula 1", "Formula 2", "Formula 3"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"STATUS", "Status"}}),
#"Lowercased Text" = Table.TransformColumns(#"Renamed Columns2",{{"Status", Text.Proper}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Lowercased Text",{{"Date Closed#(lf)*Should Be Final date", type date}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{"Date Closed#(lf)*Should Be Final date", "Closed On Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Closed On Date", type date}}),
#"Filtered Rows4" = Table.SelectRows(#"Changed Type2", each [Date Case Assigned] <> null and [Date Case Assigned] <> ""),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows4",{{"Date Case Assigned", type date}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type3", "Remarks#(lf)(Follow 5 Ws and How)", "Remarks#(lf)(Follow 5 Ws and How) - Copy"),
#"Reordered Columns1" = Table.ReorderColumns(#"Duplicated Column1",{"FileName", "TabName", "Case #", "Unique Case #", "Ticket Created", "Account Name", "Geo", "Country", "Case Type", "Queued On Date", "Priority", "Date Case Assigned", "Queued At", "CAM", "Month Reporting", "Count", "Current date", "Ticket Age", "Age Range", "Required SLA#(lf)(# of CPN)", "Actual Lead Time#(lf)", "Target Lead Time#(lf)(Good/Bad Requests)", "Actual vs Target Lead Time", "Achieved or Not Achieved (E2E LT)", "Total # of Days processing#(lf)(Actual minus redo dates)", "Total# of Days vs Target Lead Time #(lf)(V-Q)", "ACHIEVED or Not Achieved#(lf)(Internal Speed)", "Status", "Closed On Date", "Original Date Re-routed to other teams (case, ticket, or e-mail for missing docs/data)", "Date Redo back to CAM Analyst", "CRM Sales Contract", "Good Requests ?#(lf)(Y/N)#(lf)*Should NOT be blank", "# of CPNs created#(lf)*Should NOT be blank", "Shell CPN (Y/N)#(lf)*Should NOT be blank", "Shell CPN creation support", "Remarks#(lf)(Follow 5 Ws and How)", "Remarks#(lf)(Follow 5 Ws and How) - Copy", "Bad Request Delay Reason Category (Col. U)#(lf)*Closest or Main reason for the delay ", "Internal Speed - Good Request Delay Reason (Col. X)#(lf)*Should Not Be Blank ", "Specific Reason#(lf)*Specify or elaborate further reason relative to delay category", "Source of Delay #(lf)", "URGENT Request#(lf)(Y / N)#(lf)*Should NOT be blank", "Urgent Reason Category", "Explanation as to Reason for Urgency", "Review Report Generated - for Temp file#(lf)(Y / NA)#(lf)(Should not be Blank)", "Cluster Number", "Formula 1", "Formula 2", "Formula 3"}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Reordered Columns1", {{"Remarks#(lf)(Follow 5 Ws and How) - Copy", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Remarks#(lf)(Follow 5 Ws and How) - Copy"),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Remarks#(lf)(Follow 5 Ws and How) - Copy", type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Changed Type4",{{"Remarks#(lf)(Follow 5 Ws and How) - Copy", Text.Clean}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Remarks#(lf)(Follow 5 Ws and How) - Copy", Text.Trim}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text", "Remarks#(lf)(Follow 5 Ws and How) - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Remarks#(lf)(Follow 5 Ws and How) - Copy.1", "Remarks#(lf)(Follow 5 Ws and How) - Copy.2"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Remarks#(lf)(Follow 5 Ws and How) - Copy.1", type text}, {"Remarks#(lf)(Follow 5 Ws and How) - Copy.2", type text}}),
#"Renamed Columns4" = Table.RenameColumns(#"Changed Type5",{{"Remarks#(lf)(Follow 5 Ws and How) - Copy.2", "What was done at first touch"}, {"Remarks#(lf)(Follow 5 Ws and How) - Copy.1", "First Touch"}}),
#"Filtered Rows5" = Table.SelectRows(#"Renamed Columns4", each [First Touch] <> null and [First Touch] <> ""),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Filtered Rows5", {"First Touch"}),
#"Inserted First Characters" = Table.AddColumn(#"Removed Errors", "First Characters", each Text.Start(Text.From([First Touch], "en-US"), 1), type text)
in
#"Inserted First Characters"

Could it be because there is a space in between the year, month  and day?

[MarcelBeug]

Please try and put yourself in my place.

I can not see what is happening without data, so I have not the faintest idea what and where the issue would be.

Please supply some representative test data as well (Fake data of course, as long as it includes the issue you are facing).

And please indicate in which step the issue first occurs.

[danextian]

Sorry about that.

But i think I've figured  out what's wrong with the data. 

The column "(Follow 5 Ws and How)" that I t ried  to split into multiple  rows has a row in it which data is just a date (4/3/17) and no other texts. 

[MarcelBeug]

In that case you should first change your column to text.

I could create the issue, starting with the following code:

let
    Source = {"Marcel#(lf)Beug"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}})
in
    #"Changed Type"

and then adjusted step 1 to:

Source = {"Marcel#(lf)Beug", #date(2017,3,4)},

I could solve this by changing the column type to text after #"Converted To Table".

[danextian]

Hi  @MarcelBeug

Thanks for the reply. I make a lot of mistakes but also learned a lot.

By the way, what does changing 

Source = {"Marcel#(lf)Beug"},

to 

Source = {"Marcel#(lf)Beug", #date(2017,3,4)},

do? 

[MarcelBeug · Accepted solution]

Before the change, there is no error. After the change you get the error.

This illustrates that code may work fine once generated, but my cause errors when data is adjusted.

If you would recreate the code starting with Source = {"Marcel#(lf)Beug", #date(2017,3,4)}, convert to table and then split by delimiter (#(lf)), then the code would look very different, as the splitter functionality first converts the column to text:

let
    Source = {"Marcel#(lf)Beug", #date(2017,3,4)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type text}}, "en-US"), "Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}})
in
    #"Changed Type"

[danextian]

I've learned from my mistake. After loading the data into the model, I should  have set my data type first. 

Well, I was actually under the impression that ABC123 was text instead of variant