Calendar table to end of actual year

[MTam]

Hi All,

Can you help me to create a date table in Power Query, which ends time to time on last day of actual year? Also in 2018 with 12/01/2018, then in 2019 with 12/01/2019, etc...

Thank you!

[Gopal30]

You can use following code to create a date table and once the date table is created you can select the start and end date from the selector. You have to first create blank query with the following code :

//Tabla date

(StartDate as date, EndDate as date)=>

let

    //Capture the date range from the parameters

    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 

    Date.Day(StartDate)),

    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 

    Date.Day(EndDate)),

//Get the number of dates that will be required for the table

    GetDateCount = Duration.Days(EndDate - StartDate),

//Take the count of dates and turn it into a list of dates

    GetDateList = List.Dates(StartDate, GetDateCount, 

    #duration(1,0,0,0)),

//Convert the list into a table

    DateListToTable = Table.FromList(GetDateList, 

    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

//Create various date attributes from the date column

    //Add Year Column

    YearNumber = Table.AddColumn(DateListToTable, "Year", 

    each Date.Year([Date])),

//Add Quarter Column

    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 

    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

//Add Week Number Column

    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 

    each Date.WeekOfYear([Date])),

//Add Month Number Column

    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 

    each Date.Month([Date])),

//Add Month Name Column

    MonthName = Table.AddColumn(MonthNumber , "Month", 

    each Date.ToText([Date],"MMMM")),

//Add Day of Week Column

    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 

    each Date.ToText([Date],"dddd"))

in

    DayOfWeek 

Once you execute this code you will get the date selector like this:

[MTam]

Thanks but the problem with this, that it should be fully automatical, and the result table's name would be " Calendar" (for the relationships).

But good to know this solution, thank you!:)

[v-juanli-msft]

Hi @MTam

When i paste Gopal30's code in "blank query"->Advanced editor, it would generate the following function 

Then i enter 2016/1/1 in "start date" and 2018/12/31 in "end date" box, it then generate a new table with the name "Invoked Function", then i rename it as "calendar table".

Next, i can change the end date or start date of the "calendar table" by clicking on "settings" icon on the right pane.

Finally,"colse&apply", it only load "calendar table" into the data model, next time, when you need to change the end date to another, you could go to Edit Queries to change and it is a little manually.

Best regards

Maggie

[Thejeswar]

@MTam,

Can you add some more clarity to your question..? I not getting what you need??

[MTam]

Ok. I use the following code to create a dynamic calendar table. And I would like to modify thi at way, that the last date would be the last day of the actual year. Also if actual date (today's date) is in 2018 than 12/31/2018, if in 2019 than 12/31/2019, etc...

But the end date shouldn't change at all.

let
    Begin = #date(2016, 1, 1),
    DatesList = List.Dates(Begin, Number.From(DateTime.LocalNow()) - Number.From(Begin) ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(DatesList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Dates"}})
in
    #"Renamed Columns"