Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Date Hierarchy at Hourly Level

hi,   Interested in doing a date hierarchy at the current, year, quarter, week, day level, but also introducing hourly data into the hierarchy, but the big concern is all data isnt hourly, only som...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    You can create a calculated column to get the hours level. For example, I create a calculated column using the formula.

    Hour = RIGHT(Table2[Date],10)



    Then you can create a Hierarchy,  add it to HierarchySlicer as follows.



    Or you can use the hour directly in slicer, please see the following screenshot.



    In addition, you can also split the column by blank space to get hours field, but notice it changes the structure of your resource table. The following shows the Query statement and result using my sample table.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNc31zc0VDAwtDIwUIrVQREzwiJmjEXMBIuYKRYxM7BYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type time}, {"Date.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.3"})
in
    #"Removed Columns"





    Best Regards,
    Angelia

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Angelia,

 

That slicer is not working at an hourly level, its only going to daily level. The data i have is in both daily and hourly level, one table shows the daily level and another table shows the hourly level. Essentially i need to be able to dig deeper into the data via the two options indicated previously, so for example i might have 10 days daily data (daily table) but only 2 days hourly data (hourly table), i want to be able to select the drill down or next level in hierarchy buttons to go from daily to hourly, so i could go from 10 days hourly warehouse numbers and then by selecting one of the options mentioned i would then move to hourly data which might be made up of the last two days data (48 hours) plus another 10 hours if i ran at 10am on the 11th day.

 

is something like this possible, because essentially based on whether the time level is daily or hourly the values in the graphs would need to change to reference different tables

 

Regards,

Dave

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Anonymous,

You can create a calculated column to get the hours level. For example, I create a calculated column using the formula.

Hour = RIGHT(Table2[Date],10)



Then you can create a Hierarchy,  add it to HierarchySlicer as follows.



Or you can use the hour directly in slicer, please see the following screenshot.



In addition, you can also split the column by blank space to get hours field, but notice it changes the structure of your resource table. The following shows the Query statement and result using my sample table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNc31zc0VDAwtDIwUIrVQREzwiJmjEXMBIuYKRYxM7BYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type time}, {"Date.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.3"})
in
    #"Removed Columns"





Best Regards,
Angelia

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Angelia,

     

    In relation to the first image, where you have Date, Year, Month, Day and Hour, do i need to make the fields manually for Year, Month and Day (understand i need to do hourly myself). think this is the method i want, so thanks for the reply, just need clarification on the above if you dont mind.

     

    Cheers,

    Dave

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi Anonymous,

      If you value is date type, you can use it directly in hierarchy, don't need to create Year, Month and Day. Please test and mark the right reply as answer.

      Thanks,
      Angelia