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,

 

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