Forum Discussion

XaviOV's avatar
XaviOV
Icon for Helper V rankHelper V
6 years ago
Solved

Create table with date and time

Hi,

I need to create a date table with all the hours that a day has and that appear every hour, that is, from 00.00: 00 to 23:00:00, a date with every hour.
The table should start from 2016.

Cheers,

Xavi

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi XaviOV ,

     

    You can also try to enter a time table from 0:00:00-23:00:00,and then use the Crossjoin function.

    Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2020, 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

    Sample .pbix 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi XaviOV ,

     

    Replace 2020 with year (today())

    Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( YEAR(TODAY()), 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    It would be sugniffantly less overhead to build a Date Table with the Calendar funtion, and a seperate 24 hours in a day Time Table.  Can you go into more details of the end goal, maybe we can help come up with a better approach?

     

    FOrrest

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Table = GENERATESERIES(DATE(2016,1,1),DATE(2020,12,31),.04166666666666666666666666666667)

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      XaviOV you can also using Power Query , paste following code. There is start and end date which you can change as per your need.

       

      let

    StartDate = #datetime(2020, 4, 1, 0, 0, 0),
    EndDate = #datetime(2020, 4, 30, 0, 0, 0),

    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate) + 1,

    //Take the count of dates and turn it into a list of dates
    GetDateList = List.DateTimes(StartDate, GetDateCount, #duration(1,1,0,0)),
    #"Converted to Table" = Table.FromList(GetDateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateTime", type datetime}})

in
    #"Changed Type"

       

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi XaviOV ,

     

    You can also try to enter a time table from 0:00:00-23:00:00,and then use the Crossjoin function.

    Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2020, 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

    Sample .pbix 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • XaviOV's avatar
      XaviOV
      Icon for Helper V rankHelper V
      6 years ago

      Hi V-lianl-msft Thanks for the solution, I have a question,

      I see that in the formula the end of the formula is until the end of this year, but in the next year this formula will only appear until the year 2020?
      What is the way of not having to change the formula every year?

      Regards,

      Xavi

      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi XaviOV ,

         

        Replace 2020 with year (today())

        Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( YEAR(TODAY()), 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

         

        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.