Date function No Longer working

[Anonymous]

My date function is no longer working. I obtained the following function from a Microsoft Trainer:

let

    DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,

    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

    InsertYearKey = Table.AddColumn(RenamedColumns, "YearKey", each Date.Year([Date])),

    InsertYear = Table.AddColumn(InsertYearKey, "Year", each ("CY" & Number.ToText([YearKey])), type text),

    InsertQuarterKey = Table.AddColumn(InsertYear, "QuarterKey", each (([YearKey] * 10) + Date.QuarterOfYear([Date]))),

    InsertQuarter = Table.AddColumn(InsertQuarterKey, "Quarter", each ("CY" & Number.ToText([YearKey]) & "-Q" & Number.ToText(Date.QuarterOfYear([Date]))), type text),

    InsertMonthKey = Table.AddColumn(InsertQuarter, "MonthKey", each (([YearKey] * 100) + Date.Month([Date]))),

    InsertMonth = Table.AddColumn(InsertMonthKey, "Month", each (Number.ToText([YearKey]) & "-" & Date.ToText([Date], "MMM", Culture)), type text),

    InsertDateKey = Table.AddColumn(InsertMonth, "DateKey", each (([YearKey] * 10000) + (Date.Month([Date]) * 100) + Date.Day([Date]))),

    InsertDay = Table.AddColumn(InsertDateKey, "Day", each Date.ToText([Date], "yyyy-MMM-dd", Culture), type text),

    SetNumericColumns = Table.TransformColumnTypes(InsertDay, {{"DateKey", Int64.Type}, {"MonthKey", Int64.Type}, {"QuarterKey", Int64.Type}, {"YearKey", Int64.Type}}),

    DateTable = Table.ReorderColumns(SetNumericColumns, {"DateKey", "Date", "Day", "MonthKey", "Month", "QuarterKey", "Quarter", "YearKey", "Year"})

  in

    DateTable

It has been working great. But now I get the following error:

Does anyone have any useful advice. My Power BI just updated recently and I am suspectig it has something to do with the upgrade.

Thank you,

Olga

[v-xiaotang · Accepted solution]

Hi @Anonymous 

create the 2 parameters in PQ editor, set their type to Date

result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[Gabriel_Walkman]

Hi!

The first row mentions variables "EndDate" and "StartDate", but they are not defined anywhere. Where / how did the trainer define them?

[amitchandak]

@Anonymous , check do you see any issue with parameter EndDate and  StartDate