Dataflow Null Date Value

[Anonymous]

I'm receiving the following error and am not able to relolve it.  Please Help!

My existing M in PBIX file (which worked just fine, by the way!)

 #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Reordered Columns", {{"DOS", type text}}, "en-US"), "Dates", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Dates.1", "Dates.2"}),

#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DOS.1", type date}, {"DOS.2", type date}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"DOS.1", "Service Begin Date"}, {"DOS.2", "Service End Date"}}),

In Dataflows I got this gem of an error: (over and over and over again)

DataFormat.Error: We couldn't parse the input provided as a Date value

I have 6 date fields in my query - 3 of them are parsed out of other fields. So I did a process of elimination - scrapped the whole thing and started from scratch, refreshing after each parse. I thought the issue might be w/ the type not being Int64 before converting to a date, so I tried this:

#"Split column by delimiter" = Table.SplitColumn(#"Removed columns", "DOS", Splitter.SplitTextByDelimiter("-"), {"DOS.1", "DOS.2"}),

#"Changed column type 2" = Table.TransformColumnTypes(#"Split column by delimiter", {{"DOS.1", type text}, {"DOS.2", type text}}),

#"Split column by delimiter 1" = Table.SplitColumn(#"Changed column type 2", "DOS.1", Splitter.SplitTextByDelimiter("/"), {"DOS.3", "DOS.4", "DOS.5"}),

#"Changed column type 3" = Table.TransformColumnTypes(#"Split column by delimiter 1", {{"DOS.3", Int64.Type}, {"DOS.4", Int64.Type}, {"DOS.5", Int64.Type}}),

#"Merged columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed column type 3", {{"DOS.3", type text}, {"DOS.4", type text}, {"DOS.5", type text}}), {"DOS.3", "DOS.4", "DOS.5"}, Combiner.CombineTextByDelimiter("/", QuoteStyle.None), "Service Begin Date"),

#"Split column by delimiter 2" = Table.SplitColumn(#"Merged columns", "DOS.2", Splitter.SplitTextByDelimiter("/"), {"DOS.3", "DOS.4", "DOS.5"}),

#"Changed column type 4" = Table.TransformColumnTypes(#"Split column by delimiter 2", {{"DOS.3", Int64.Type}, {"DOS.4", Int64.Type}, {"DOS.5", Int64.Type}}),

#"Merged columns 1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed column type 4", {{"DOS.3", type text}, {"DOS.4", type text}, {"DOS.5", type text}}), {"DOS.3", "DOS.4", "DOS.5"}, Combiner.CombineTextByDelimiter("/", QuoteStyle.None), "Service End Date"),

#"Replaced value" = Table.ReplaceValue(#"Merged columns 1", "//", "", Replacer.ReplaceText, {"Service End Date"}),

#"Changed column type 5" = Table.TransformColumnTypes(#"Replaced value", {{"Service Begin Date", type date}, {"Service End Date", type date}}),

But I still get the error.

I believe that the error is happening because of the null values in my "Service End Date" column. But I'm not sure how to resolve the error.

When I'm working in Power Query, it's not giving me an error... it's only when I go to refresh the dataflow.

     

[Jimmy801]

Hello @Anonymous 

what do you mean by "Dataflow"?

I could think of a row formated as a date instead of text, and so the parse-function isn't able to apply your request. In this case you need to pass this variable #"Changed column type 2" into a Table.TranformColumnTypes to change it to text.

By the way... why do you parse the column multiple times to remerge it?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

I'm using dataflows on the power BI service. It's essentially Power Query online - but its just a little different than PQ in PBI Desktop.

I'm initially parsing out the dates because they're a part of a string - the first one I parse is a six digit date, and the only way that I know to get it into date format is to parse it out and then merge with the "/" as the separator.

In my example, I initially didn't have to parse it out a second time (and maybe don't need to at all) I was just trying to see if it would clear my error.

[Jimmy801]

Hello @Anonymous 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous 

this is what might happen, that you don't have a text string on your whole column, but somewhere at the end a date. And it's not possible to parse a date. So you have to do a transform.columntype of this column before, and change it to text. Did you try this?

Here an example of what happen when you try to split a date

let
	Source = #table
	(
		{"Date"},
		{
			{"03.01.19-04.01.19"},	{"43468"}
		}
	),
	ToDate = Table.TransformColumns(Source, {{"Date", each try Date.From(Number.From(_)) otherwise _}}),
	Split = Table.SplitColumn(ToDate, "Date", Splitter.SplitTextByAnyDelimiter({"-"}))
in
	Split

By the way... you don't need to parse it by "/". Just use a transformColumns with a Date.FromText(_,"en-US") i suppose

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

also, I've tried trimming/cleaning and replacing nulls with blank, but it hasn't worked yet.

[Mariusz]

Hi @Anonymous 

Do you get errors after converting to Date type, it most probably is the issue?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

I don't actually ever get the error in power query... it's only on the refresh of the dataflow... but, yes, it is after converting to Date format. I don't know how to clear the error? I need the columns formatted as a date.

I'm pretty certain that it is just on the End date column because of the null values.