Forum Discussion
Solved
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() funct...
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
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?
Yes, you should provide the same arguments to #duration: (0,0,1,0), not (0,1,0)