Search code examples
datetimepowerbi-desktop

PowerBI Create Table with date AND time with 1 second increment

I am sure you can help me out as I am unable to figure out an easy solution.

Goal:

  • I would like to create a table with date and time and 1 second increment

Initial setup:

  • 2 datetime tables
  • min/max for every datetime-table determined via measure
  • Generated new date OR time series via Generateseries out of the min/max-values of the 2 tables

Question: Am I correct, that it is not straight forward to get a table with date and time counting up between the min/max-values I generated? Do I need to combine date and time afterwards to get a table containg both counting up?

Personal remark: This issue seems trivial to me and I don't know if I am mistaken but it seems to me as PowerBI does lack the feature to work with both (date&time) and only does one at a time natively via Generateseries/Datevalue or Generateseries/Timevalue. Also slicer do trouble with date&time.

Images: One image shows the standard solution found widely to create a table with seconds, minutes or hours counting. One can also use DATEVALUE to create a table with the date counting. The other image shows the datetime-values used. The sample data is from 11.04.2022 but could also be more than one day.

[enter image description here](https://i.sstatic.net/1JDxMx3L.png)

I tried the standard solutions found in the internet but didn't find an easy way to generate a table containing date and time without putting it together afterwards. Also I do have trouble doing so as it is a little more tricky to me as I am relatively new to POWERBI.

Solution

  • you can try to build the datetime table in Power Query

    let

_list={List.Min(Plot[Datetime]),List.Min(Deckel[Datetime]),List.Max(Plot[Datetime]),List.Max(Deckel[Datetime])},
StartDate = List.Min(_list),

EndDate = List.Max(_list),


Duration = Duration.TotalSeconds( Duration.From(EndDate-StartDate))+1,
 


Dates = List.DateTimes(StartDate, Duration, #duration(0,0,0,1)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

    enter image description here