Automatic generation of date and time data of power query

[まもる]

Please let me know if you know.
I want to automatically generate date and time data every 5 minutes with "power query".

For example:
Start date and time 2018/11/20 0:45:00, End time is continuous.

The data I want (every 5 minutes)
2018/11/20 0:45:00
2018/11/20 0:50:00
2018/11/20 0:55:00
2018/11/20 1:00:00
2018/11/20 1:05:00
2018/11/20 1:10:00
2018/11/20 1:15:00
・
・
2018/11/26 10:25:00
2018/11/26 10:30:00

I need it in Power Query so I can then perform a merge to another table later.

Hope someone can help with this.

Best Regards,

[Ashish_Mathur · Accepted solution]

Hi,

This M code will create 10 Date/Time entries

let
    Date = List.DateTimes(#datetime(2018, 11, 20, 00, 45, 0), 10, #duration(0, 0, 5, 0))
in
    Date

Read more here - https://docs.microsoft.com/en-us/powerquery-m/list-datetimes

Hope this helps.

[まもる]

@Ashish_Mathur

@Anonymous

Hello.
Thank you for your information.
It's perfect!

Thank you very much.

[Ashish_Mathur]

You are welcome.

[まもる]

@Ashish_Mathur

I'm so sorry.  I have an additional question.
Instead of creating a list of 10 values, Can you make it infinite?

I can not speak English.  I'm sorry if English was wrong.
Thank you.

[Ashish_Mathur]

Hi,

Specify the year as 2999.  It should be as good as infinite

[まもる]

Hello.  Thank you for your information.
But, How can I specify the year as 2999?

This is a mistake.
-----
= List.DateTimes(#datetime(2018, 11, 20, 00, 45, 0), EndOfYear 2999, #duration(0, 0, 5, 0))
-----

Please, help me.

[Ashish_Mathur]

Hi,

This should give you 3000 lines

Date = List.DateTimes(#datetime(2018, 11, 20, 00, 45, 0), 3000, #duration(0, 0, 5, 0))

[まもる]

Hello.  Thank you for your advice.

I need to merge the date and time data created here with the continuation data of another table.
（The data to be merged is added every 5 minutes.）
So, 3000 lines is not enough.

Do you have any good ideas?

[Ashish_Mathur]

You are welcome.  No, i do not hav any better ideas.

[まもる]

Hello.
Thank you very much for letting me know many things for a long time.

Best Regards,

by Mamoru

[Anonymous]

Oh thats way better. I'm shameless stealing this.

[Ashish_Mathur]

Thank you.

[Anonymous]

Create 2 tables.  One table with all of the dates you want.  The other with all of the times you want.  Do a Merge of these tables by adding a column references the entire other table.

Here is an example:

Dates:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMLRQitWJVjJC5hjDObEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

Times:

DateTimes:

let
    Source = Dates,
    #"Added Merge" = Table.AddColumn(Source, "Times", each #"Times"),
    #"Expanded Times" = Table.ExpandTableColumn(#"Added Merge", "Times", {"Times"}, {"Time"}),
    #"Added Combined" = Table.AddColumn(#"Expanded Times", "Custom", each Text.From([Date]) & " " & Text.From([Time])),
    #"Change to DateTime" = Table.TransformColumnTypes(#"Added Combined",{{"Custom", type datetime}})
in
    #"Change to DateTime"