Forum Discussion
Refresh error "We cannot convert the value null to type Date"
My pbix was refreshing fine in the service until yesterday. Today, it fails with the error "We cannot convert the value null to type Date" in the Date table. I didn't modify anything in my pbix. I also checked if I had any null values in the date table's "Date" column prior to the change type to date step. There is none. I even tried adding a remove null and "" step prior to the change type step. It didn't work. Does anybody have an idea what is happening? By the way, I am able to refresh fine in PBI Desktop.
Here's my full query code:
let
CutOff = MaxDate,
Source = MinDate,
Custom = List.Dates(Source, Number.From(MaxDate) - Number.From(MinDate)+1 ,#duration(1,0,0,0)),
#"Convert List to Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Change Type" = Table.TransformColumnTypes(#"Convert List to Table",{{"Column1", type date}}),
#"Rename Column" = Table.RenameColumns(#"Change Type",{{"Column1", "Date"}}),
Year = Table.AddColumn(#"Rename Column", "Year", each Date.Year([Date]), type number),
#"Month Number" = Table.AddColumn(Year, "Month Number", each Date.Month([Date]), type number),
#"Month Name" = Table.AddColumn(#"Month Number", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text),
Qtr = Table.AddColumn(#"Month Name", "Qtr", each "Q" & Number.ToText(Date.QuarterOfYear([Date])),type text),
#"Changed Type" = Table.TransformColumnTypes(Qtr,{{"Year", Int64.Type}, {"Month Number", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}})
in
#"Sorted Rows"
Best regards,
Ferdinand
2 Replies
- GilbertQSuper UserWould it be possible to see if what version your Gateway is on?
What does this line do?
#"Convert List to Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error),- AnonymousNot applicable
Hi GilbertQ,
I ended up getting rid of the Date table and just used the date column from my Daily Rates table. I'm now able to refresh in the service without the error. But here's to answer your questions. My gateway version is below.
The line
- #"Convert List to Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
is to convert the generated list of dates from the previous line
- = List.Dates(Source, Number.From(CutOff) - Number.From(Source) +1 ,#duration(1,0,0,0))
Best regards,
Ferdinand