Forum Discussion
Created Table Date/Hour
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
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
3 Replies
This page has a sample script for this and best practices.
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
