Forum Discussion

duggy's avatar
duggy
Advocate II
7 years ago
Solved

Generate date column using generate series

Hi,   I have the following formula that generates a new table with a row for each day between a start and end date from a given table:   Events Table = SELECTCOLUMNS ( GENERATE ( 'sp...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi duggy,

    Based on my test, you could refer to below steps:

    Create a calender table:

    Date Table = CALENDAR(MAX('Table2'[startspecialdaydate]),MAX('Table2'[Endspecialdaydate]))

    Create a new table:

    Result Table = SUMMARIZECOLUMNS('Table1'[StoreId],'Table1'[specialdayname],'Date Table'[Date])

    Result:

     

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

As long as data in the 3rd and 4th columns are actual dates, you should be able to get your desired result by using the following M code

 

={Number.From([startspecialdaydate[)..Number.From([Endspecialdaydate])}

duggy's avatar
duggy
Advocate II
7 years ago

Hi,

 

Sorry, not following. Are you saying to create a new column in the specialdays table and use the code:

theeventdates = {Number.From([startspecialdaydate])..Number.From([Endspecialdaydate])}

 

I get the syntax for 'From' is incorrect when I try this.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    That M code has to be written int he Query Editor.  In the Query Editor, go to Add Column > Custom column