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 !

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 !