Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Categorizing a column of data into specific measures

Hello everyone,

I have a set of data that is consolidated from a data source which consists of employees' signoff date and time. I would like to categorize the entire data into hour based (which is from 7am to 7pm) to another column. For me to do so, is it possible to categorize the entire data in the consolidated worksheet?

Appreciate the feedback from you guys. Thanks! 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    you basicly just need to add a custom column to your data using this function

    Time.Hour([Tasklist Signoff Date and Time])

     

    this gets the hour of signoff.

    To visualize this, just group this new colum, applying a count rows.

     

    Here a complete practical example how it looks like. To implement this in your real world, just replace the first to steps (Source and ToDateTime) and replace it by your first two steps

    let
	Source = #table
	(
		{"Tasklist Signoff Date and Time"},
		{
			{"43849,3333333333"},	{"43849,3541666667"},	{"43849,3749998843"},	{"43849,3958331597"},	{"43849,4166664352"},	{"43849,4374997106"},	{"43849,4583329861"},	
			{"43849,4791662616"},	{"43849,499999537"},	{"43849,5208328125"},	{"43849,541666088"},	{"43849,5624993634"},	{"43849,5833326389"},	{"43849,6041659144"},	
			{"43849,6249991898"},	{"43849,6458324653"},	{"43849,6666657407"},	{"43849,6874990162"},	{"43849,7083322917"},	{"43849,7291655671"},	{"43849,7499988426"},	
			{"43849,7708321181"},	{"43849,7916653935"},	{"43849,812498669"}
		}
	),
    ToDateTime = Table.TransformColumns
    (
        Source,
        {
            {
                "Tasklist Signoff Date and Time",
                each DateTime.From(Number.From(_)),
                type datetime
            }
        }
    ),
    Hour = Table.AddColumn(ToDateTime , "Hour", each Time.Hour([Tasklist Signoff Date and Time])),
    Group = Table.Group(Hour, {"Hour"}, {{"Hour.1", each Table.RowCount(_), type number}})
in
    Group

     


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

7 Replies

  • AnkitBI's avatar
    AnkitBI
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi Anonymous  

    Can you share the expected result in new Column.

     

    Thanks

    Ankiit Jain

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi AnkitBI ,

      So basically, I would like to categorize and get the data based on hour, for example from 8am to 9am. 

      Assuming 29 employees signoff at 8am to 9am, therefore the column "8am" should count 29 employees as shown below.

       

      I do not know if it's possible to do so in the consolidated worksheet as I'm trying other options at the moment, which is by using power pivot. My current Pivot table looks like this:


       

      Thanks!

      • AnkitBI's avatar
        AnkitBI
        Icon for Solution Sage rankSolution Sage
        6 years ago

        Hi Anonymous 

        Try if below works for you. In sample "GetCat" I have captured 2-3 Categories, you will need to expand for others. This will provide you Time Category, you can use that in Report to get counts.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNU3MjAyUDA0tjIyUYrVQRazwBTCpoxYMQsrYwMytVqSrxWPWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"SignOff Dates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SignOff Dates", type datetime}}),
    GetTime = Table.AddColumn(#"Changed Type","GetTime",each DateTime.Time([SignOff Dates])),
    GetCat = Table.AddColumn(GetTime,"TimeCat",each if([GetTime] > #time(8,0,0) and [GetTime] < #time(9,0,0)) then "8-9" else if([GetTime] > #time(13,0,0) and [GetTime] < #time(14,0,0)) then "13-14" else "others")
in
    GetCat

         

         Thanks
        Ankit Jain
        Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.