Create Calendar Table with row per minute

[Alex1602]

Hi together,

For Data synchronization issues I want to create a calendar table with one row per minute.

Is there a way to do this in Power BI and how can it be done? With the Calendar() function, I can just create a table with one row per day.

[MarcelBeug · Accepted solution]

It can be done in the query editor, e.g. for 2017

Table.FromColumns({List.DateTimes(#datetime(2017,1,1,0,0,0),365*1440,#duration(0,0,1,0))}, type table[DateTime=datetime])

[MarcelBeug · Accepted solution]

It can be done in the query editor, e.g. for 2017

Table.FromColumns({List.DateTimes(#datetime(2017,1,1,0,0,0),365*1440,#duration(0,0,1,0))}, type table[DateTime=datetime])

[yossifisch]

---

@MarcelBeug wrote:

It can be done in the query editor, e.g. for 2017

Table.FromColumns({List.DateTimes(#datetime(2017,1,1,0,0,0),365*1440,#duration(0,0,1,0))}, type table[DateTime=datetime])

---

I tried to create a time table without dates using your suggestion with modifications:

Table.FromColumns({List.Times(#time(0,0,0),1440,#duration(0,1,0))}, type table[Time=time])

However, I am getting the following error:

Expression.Error: 3 arguments were passed to a function which expects 4.
Details:
    Pattern=
    Arguments=List

Can you explain why that happens?

[MarcelBeug]

Yes, you should provide the same arguments to #duration: (0,0,1,0), not (0,1,0)

[MarcelBeug · Accepted solution]

There was another post questioning if this could be done for the last 6 months or so. This post was removed, while I was preparing my answer.

This query will produce the table starting on the first, 6 months ago (so currently Decmber 1, 2016) and ends on the last day of the current mont (curently June 2017), 11:59 PM:

let
    Start = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-6),
    End = Date.AddMonths(Start,7),
    Count = Number.From(End-Start)*1440,
    Calendar = Table.FromColumns({List.DateTimes(Start,Count,#duration(0,0,1,0))}, type table[DateTime=datetime])
in
    Calendar

[Alex1602]

Yes, I removed the question, because I thought I had the solution already with the following Query:

= Table.FromColumns({List.DateTimes(Date.AddDays(DateTime.LocalNow(),-180),180*1440,#duration(0,0,1,0))}, type table[DateTime=datetime])

But then I realised that this calendar produced duplicate Rows on March 26, when the time shift occurred.

And strange thing, I could not remove the duplicate Rows with the Built in function!

But luckily, you came around with your solution and this one does not have duplicate rows 🙂

Thank you so much, again!