Categorizing a column of data into specific measures

[Anonymous]

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! 

[AnkitBI]

Hi @Anonymous  

Can you share the expected result in new Column.

Thanks

Ankiit Jain

[Anonymous]

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]

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.

[Anonymous]

Hi @AnkitBI ,

Thanks for your script. However, I am having a difficult time trying to encrypt your script into mine. Below is my script for the consolidated data.

let
    Source = Excel.Workbook(File.Contents("G:\XXXXXX.xls"), null, true),
    #"Valve Task List Completion Deta1" = Source{[Name="Valve Task List Completion Deta"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Valve Task List Completion Deta1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Container", type text}, {"Parent Container", type text}, {"Container Status", type text}, {"Material", type text}, {"Material Desc", type text}, {"Material Rev", type text}, {"Model", type text}, {"Serial #", type text}, {"SAP Batch", Int64.Type}, {"Original Qty", Int64.Type}, {"Current  Qty", Int64.Type}, {"Final Confirmed Qty", Int64.Type}, {"Production Order", Int64.Type}, {"Production Order Type", type text}, {"SWR #", type text}, {"Tasklist Completion Work Cell", type text}, {"Task List", type text}, {"Task List Rev", type text}, {"Task List Desc", type text}, {"Submitter", type text}, {"Tasklist Signoff Date and Time", type datetime}, {"Sign Off Qty", Int64.Type}, {"Product Family", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "VAL"}, {"Column2", "ZPK1"}})
in
    #"Renamed Columns"

  To input your script into mine, where do I have to start at?

Thank you very much! 

[Jimmy801 · Accepted solution]

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

[Anonymous]

Hi @Jimmy801 

Thank you! The code is exactly what I need. I add a custom column using the code you gave and it worked. Thank you once again!

[Anonymous]

Thank you @AnkitBI !

I'll give it a try and let you know if it works!