Forum Discussion

artp3642's avatar
artp3642
New Member
4 years ago
Solved

Transform weekly data into hourly data

So the situation is as follows, I have weekly records of events and i want to make it hourly, logically every hour will have the same value, that corresponds to the whole week, but i need it to be ho...
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi  artp3642  , 

     

    I am sorry i couldn't entirely understand what does your weekly record look like and hourly look like?

     

    generate(filter( generate(summarize(Date, date[Date], Date[D WeeK end Date]), Table) , [Week End Date] = [D WeeK end Date]),[Hour Table])

    The first Date means a table. It is the first argument of summarize().

     

    Actually, you can create a Date table contains Year,Month,Day, Weeknum,Hour. Then create a relationship between Date table and Fact table, which is one to many and depended on Date/time column.

    By this code:

    Date = GENERATESERIES(DATE(2022,1,1),DATE(2022,12,31),TIME(1,0,0))
     
    Model:

     

    Then you can plot with other hourly date.

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

amitchandak's avatar
amitchandak
Super User
4 years ago

artp3642 , Assume you have data with week end date , some number

 

create a date table with week end date ,

 

Hour Table = generateseries(1,24,1)

generate(filter( generate(summarize(Date, date[Date], Date[D WeeK end Date]), Table) , [Week End Date] = [D WeeK end Date]),[Hour Table])

 

if needed divide the value column by 7*24 to allocate

 

artp3642's avatar
artp3642
New Member
4 years ago

 

amitchandak 

so first i create a table clalled hour/-table and then create a column using the followeing expression? 

generate(filter( generate(summarize(Date, date[Date], Date[D WeeK end Date]), Table) , [Week End Date] = [D WeeK end Date]),[Hour Table])

I didnt understand what Date in the first argument mean, is it a table, or what, and neither Table as the last argument