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

 

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