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.
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!
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:
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!:)
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
Can you add some more clarity to your question..? I not getting what you need??
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"
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.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |