Forum Discussion

igaca's avatar
igaca
Helper III
10 years ago

Power Query /M version of CALENDARAUTO() DAX function?

Am curious if there is a comparable function in M which looks at the date fields in the available queries and generates a contiguous date table from first to last date value?   I am wanting to gene...
Need Help
Tutorial Requests
Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

igaca

 

Do you mean replicate a calender table as above or an equvalent to CALENDARAUTO? For former it would be not to duplicated. For latter

 

Again

According to the BOL, CALENDARAUTO returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model. Based on my test, in the model means it would detect the date low and high year boundaries in all tables. Though I'm not familar with Power query, I'd doubt there's an equivalent in Power query.

 

 

  • igaca's avatar
    igaca
    Helper III
    10 years ago

    I was looking for an equivalent to DAX CALENDARAUTO() in M.  If someone has managed to work out a solution, an input would be greatly appreciate.  Meanwhile, current setup I utilize is not a bad way to go either.

     

    Thank you,

     

    Igor

    • delswingle's avatar
      delswingle
      Regular Visitor
      9 years ago

      Igor,

       

      I was trying to tackle a similar problem the other day. I created a function to find the MIN date which I call when creating the date table. I used the current time as it better suited my needs but you could also created a second function to find the MAX to find both ends of your date spectrum of a specified table/column.

       

      ***Function Start Date***
      (StartDate) =>
      let
      Source = Sales,
      StartDate = Table.Group(Source, {}, {{"MinDate", each List.Min([Order Date Key]), type datetime}})[MinDate]{0}
      in
      StartDate

       

       

      ***Start of Date Table*** 

      let
      Start = Number.From(StartDate(null)),
      End = Number.From(DateTime.LocalNow()),
      Source = Date(StartDate(null), End - Start, #duration(1, 0, 0, 0)),
      .....