Forum Discussion

RMDNA's avatar
RMDNA
Solution Sage
7 years ago
Solved

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

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?

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

2 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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