Forum Discussion
Super User[Expression.Error] We cannot convert the value #date(2017, 4, 3) to Type Text
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"
Super UserSorry 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.
Community ChampionIn 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
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