create empty dates / hours

[Lindserke]

Hi, 

i'm quite new to Power BI, and i'm not really finding the right subject on the internet to help me.

I have a large set of data, and would like to transform it as follows

AS IS:

01/01/2018 08:01:05

01/01/2018 08:05:17

01/01/2018 08:08:56

01/01/2018 08:17:45

01/01/2018 09:01:43

TO BE

01/01/2018 - 08:00:00 - 3

01/01/2018 - 08:15:00 - 1

01/01/2018 - 08:30:00 - 0

01/01/2018 - 08:45:00 - 0

01/01/2018 - 09:00:00 - 1

Basically, i want to do 2 things:

1/ group data by 15 minutes (I already managed that by adding a new colon "kwartuur", and then merging "hour" with "Kwartuur")

2/ but I cannot figure out how to create the date/hour that is missing in the basic data set (8:30).

i tried to create a calendar and a time calendar, but from there, how am i supposed to merge both data sets?

Thanks in advance for you feedback !

[v-yingjl]

Hi @Lindserke ,

You can follow these steps to try:

1. Create an index column in power query editor, close and apply it.

2. Create this calculated column:

Result =
VAR _minute =
    SWITCH ( MOD ( [Index], 4 ), 1, 0, 2, 15, 3, 30, 0, 45 )
VAR _time =
    FORMAT ( TIME ( HOUR ( 'Table'[Date] ), _minute, 0 ), "hh:nn:ss" )
RETURN
    DATE ( YEAR ( 'Table'[Date] ), MONTH ( 'Table'[Date] ), DAY ( 'Table'[Date] ) ) & "-" & _time & "-" & 'Table'[kwartuur]

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Lindserke]

hi @v-yingjl ,

thanks for the reply,

however, i'm still not able to get this thing to work... 

i tried to upload my sample file, so you can maybe get a quick glance of what i'm doing, and seeing what i'm not doing correct, but i dont find a way to upload a file... 

[v-yingjl]

Hi @Lindserke ,

If you want to upload the sample file in the community, you can upload the file to somewhere can store files like OneDrive for Business etc. first, then share the link in the community, it will allow us to better help you solve the issue. One point you should note is that please replace the sensitive information in the sample file.

Best Regards,
Yingjie Li

[Lindserke · Accepted solution]

i've found a bit of a solution :)!

for those who have the same issue as me, I did the following

- create date calendar: 

https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

- create time calendar: https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

slightly adapted:

TimeTable =

VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23), "Hour", [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value])

RETURN

ADDCOLUMNS(

CROSSJOIN(HourTable, MinuteTable, SecondsTable),

"Time", TIME([Hour], [Minute], [Second]),

"TimeRound", Time([Hour],[Minute],0),

"TimeKwartuur",time([Hour],(if ([Minute]<15,0,if ([Minute]<30,15,if ([Minute]<45,30,45)))),0)

)

- link tables:

- create table using Date and Kwartuur from the created tables:

i'm still struggling with the fact that he is only showing me  the hours where there was input (so for example, nothing before 6:30), but i'm already a bit more on track

thanks for the advice, it put me on the right track !

[amitchandak]

@Lindserke , I would recommend separate date and time and create date and time dimension table

Date = [Date time].date

Date = [Date time].time

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Lindserke]

Hi @amitchandak,

thanks for the quick reply. Data is split by date and hour, i should have specified that one 🙂

I indeed created calendar / time table as suggested by Kohera.be.

but when i'm making a matrix, i'm only seeing the "used" dates/times, and not the empty ones... plus, i'm seeing the exact time, not the "kwartuur"

maybe i'm doing something wrong with the connections?

i'm not sure how to make this work.