Created Table Date/Hour

[Anonymous]

Hello to all.

I am asking for your help in creating a time table.
I would like to simulate a table going from 01/06/2021 at 00:00:00 to 01/07/2021 at 29:59:59 with each row incremented by one second.
I managed to do it in DAX with this code:
dim_date =
VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Heure", [Value])
VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])
VAR SecondeTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Seconde", [Value])
RETURN
ADDCOLUMNS(CROSSJOIN(CALENDAR(DATE(2021,06,1),DATE(2021,07,1)), ADDCOLUMNS(CROSSJOIN(HourTable,MinuteTable,SecondeTable),"Heure/Min/Seconde",TIME([Heure],[Minute],[Seconde]))),"DT",[Date]+[Heure/Min/Seconde])

But I would like to do it as a query in power query with the M language. Could you please help me?

Thanks in advance,

Joël

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = List.DateTimes(#datetime(2021,1,6,0,0,0), Duration.TotalSeconds(#datetime(2021,1,7,23,59,59)-#datetime(2021,1,6,0,0,0))+1, #duration(0,0,0,1)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}})
in
    #"Changed Type"

Pat

[Anonymous]

Hello @mahoneypat,

Thank you very much for your help it works perfectly,

Have a nice day,

Cdt

[jennratten]

This page has a sample script for this and best practices.

https://radacad.com/script-for-creating-time-table-in-power-bi-with-hours-minutes-and-seconds-bucket...