Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
is to convert the generated list of dates from the previous line
Best regards,
Ferdinand
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.