Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date function No Longer working

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

  • 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.

     

3 Replies

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

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Continued Contributor

    Hi!

     

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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support

    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.