Though experiment - dynamic date table based on date MIN/MAX?

[RMDNA]

Hi all,

I generally create my date tables using something like the following, so I can set start and end parameters, and customize from there.

Spoiler

//Create Date Dimension

(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

 

However, I had a thought - can I make a date table using the MIN and MAX of a date column as the start and end date parameters, to ensure that a) I only have as many dates as I need, and b) that my date table will grow as my data does?

[v-danhe-msft]

Hi @RMDNA,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @RMDNA,

Based on my test, you could use the query parameter to get your result:

Here is a sample table

Create two queries to fetch the max/min value:

Max query:

let
    Source = List.Max(#"Table1"[Column1]),
    #"Converted to List" = {Source}
in
    #"Converted to List"

Min query:

let
    Source = List.Min(#"Table1"[Column1]),
    #"Converted to List" = {Source}
in
    #"Converted to List"

Use the parameter to pass the query:

Result:

Hoep it could help you.

Regards,

Daniel He