Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
90 | |
71 | |
69 |
User | Count |
---|---|
228 | |
128 | |
117 | |
83 | |
82 |