Forum Discussion

Lindserke's avatar
Lindserke
Regular Visitor
6 years ago
Solved

create empty dates / hours

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/2...
  • Lindserke's avatar
    Lindserke
    6 years ago

    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 !

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

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's avatar
Lindserke
Regular Visitor
6 years ago

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... 

 

  • Lindserke's avatar
    Lindserke
    Regular Visitor
    6 years ago

    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 !

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    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