Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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])
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
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 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)
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
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!
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |