Dynamic minimum and maximum dates using M/Power Query

Question

I have a calendar table that is created, on-the-fly, using M.

It starts like this:

let
    StartDate = #date(2016, 1, 1),
    EndDate = #date(2018, 12, 31),
    //Used for 'Offset' Column calculations, you may Hard code CurrentDate for testing e.g. #date(2017,9,1)
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    // Specify the last month in your Fiscal Year, e.g. if June is the last month of your Fiscal Year, specify 6
    FiscalYearEndMonth = 6,
    #"==SET PARAMETERS ABOVE==" = 1,
    #"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    ...
    ...

Is it possible to make the first two lines dynamic so that they pick up minimum and maximum dates from database tables? So these two lines:

let
    StartDate = #date(2016, 1, 1),
    EndDate = #date(2018, 12, 31),

I have another table being loaded into the model using the following - can I somehow use the Date column from this loads to dynamically set StartDate and EndDate ?

let
    Source = Sql.Database("ourServer", "ourDB"),
    tb_ModelFact = Source{[Schema="dbo",Item="tb_ModelFact"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(tb_ModelFact,{{"Date", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each true),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Amount", type number}})
in
    #"Changed Type1"

Edit

So I tried this

StartDate = List.Min(Fact[Date]),
EndDate = List.Max(Fact[Date]),

....and got this mysterious error?

Expression.Error: We cannot convert the value #datetime(2016, 1, 6, 0, 0, 0) to type Date.
Details:
    Value=06/01/2016 00:00:00
    Type=Type

Is this error in subsequent M code after the declaration?

Alexis Olson Alexis Olson · Accepted Answer · 2017-12-18T17:31:13

Yes. You should be able to write something along these lines:

let
    StartDate = List.Min(tb_ModelFact[Date]),
    EndDate = List.Max(tb_ModelFact[Date]),

where tb_ModelFact[Date] is the column that has the dates you are trying to take the max and min from.

You will need to change tb_ModelFact to whatever the name of that second query is though.

Dynamic minimum and maximum dates using M/Power Query

1 Answers