Forum Discussion
Create Calendar Table with row per minute
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.
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])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
5 Replies
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])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 CalendarYes, 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!
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=ListCan you explain why that happens?
Yes, you should provide the same arguments to #duration: (0,0,1,0), not (0,1,0)
