Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Would 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),
    • Anonymous's avatar
      Anonymous
      Not 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