Comparing Time in PowerBI and Creating a new column

[arvindlearns]

Hi All,

I have a column which has data in the following format as below: - 

20-Feb-17 07:02 pm
05-Oct-16 07:28 pm
12-Dec-16 03:59 am
10-Feb-17 04:38 am
06-Feb-17 10:00 am
07-Nov-16 03:25 am
20-Jan-17 04:06 pm
08-Dec-16 02:52 pm
16-Dec-16 04:19 pm
21-Feb-17 03:49 pm
17-Oct-16 06:07 am
06-Jan-17 11:35 am
04-Jan-17 01:23 am
19-Oct-16 12:47 pm
23-Nov-16 12:40 pm
01-Feb-17 08:26 am
25-Feb-17 11:33 pm
14-Feb-17 11:01 am

I want to create a column called "Shift" which uses the above data based on time to identify the shifts that the Remedy tickets come in... The shift timings as below: - 

Shift Timings:
Shift 1: 6:30 pm CST – 3:30 am CST
Shift 2: 12:30 am CST – 9:30 am CST
Shift 3: 9:30 am CST – 6:30 pm CST
Shift 1/Shift2: 12:30 am CST – 3:30 am CST

Regards

Arvind

[MarcelBeug · Accepted solution]

Ok. I'm afraid you mixed things up.

At the end of your #"Changed Type" step, I see Source,{"Date and time", type datetime}}),

Remove the red part.

Next, identify which of your fields represent the date/times from your example in the original post.

My best guess would be the field "Created".

This name should be put in my formula instead of the field name "Date and time" that I used in my file.

So in case it is field "Created":

#"Added Custom" = Table.AddColumn(#"Changed Type", "Shift", (x) => Table.Last(Table.SelectRows(ShiftTimings, each [Start] <= DateTime.Time(x[Created])))[Shift])
in
#"Added Custom"

[MarcelBeug]

Left your Data, in the middle table ShiftTimings (also loaded in PQ with connection only), at the right the output from DataWithShifts with code:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date and time", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Shift", (x) => Table.Last(Table.SelectRows(ShiftTimings, each [Start] <= DateTime.Time(x[Date and time])))[Shift])
in
    #"Added Custom"

[arvindlearns]

Thanks MarcelBeug for the Solution. I seem to be hitting a new issue. I am new to the World of Both PowerBI and Power Query(M)

My PowerBI has 2 datasets

1. My data with approx 20 columns

2. ShiftTimings as provided in the solution

The contents in my Advanced Editor is as below:

let
Source = Excel.Workbook(File.Contents("Remedy Ticket Analysis.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number", type text}, {"Status", type text}, {"Priority", type text}, {"Assigned to", type text}, {"Task type", type text}, {"Opened by", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Updated by", type text}, {"Closed", type datetime}, {"Closed by", type text}, {"Due date", type datetime}, {"Categorization", type text}, {"Actual start date", type datetime}, {"Actual end date", type datetime}, {"Made SLA", type logical}, {"Active", type logical}, {"Meets SLA", type text}, {"Empty Closed Week", type datetime}, {"Assigned Organization", type text}, {"Closed Organization", type text}, {"Aging Dates", Int64.Type}, {"Aging Interval", type text}, {"BackLogs Flag", type text}, {"Assignment Group", type text}, {"Description", type text}, Source,{"Date and time", type datetime}}),

Below is what I added from the solution you provided
#"Added Custom" = Table.AddColumn(#"Changed Type", "Shift", (x) => Table.Last(Table.SelectRows(ShiftTimings, each [Start] <= DateTime.Time(x[Date and time])))[Shift])
in
#"Added Custom"

I am getting an error: "We cannot convert a value of type Table to type List"

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=Table
Type=Type

[MarcelBeug · Accepted solution]

Ok. I'm afraid you mixed things up.

At the end of your #"Changed Type" step, I see Source,{"Date and time", type datetime}}),

Remove the red part.

Next, identify which of your fields represent the date/times from your example in the original post.

My best guess would be the field "Created".

This name should be put in my formula instead of the field name "Date and time" that I used in my file.

So in case it is field "Created":

#"Added Custom" = Table.AddColumn(#"Changed Type", "Shift", (x) => Table.Last(Table.SelectRows(ShiftTimings, each [Start] <= DateTime.Time(x[Created])))[Shift])
in
#"Added Custom"

[arvindlearns]

Thanks for the quick assistance and help...

In the statement DateTime.Time(x[Created])))[Shift]), what does the x stand for...

I tried the changes that you stated and it worked brilliantly well

Regards

Arvind

[MarcelBeug · Accepted solution]

The answer to the question what the x stand for, is a bit complicated.

First a simple example, suppose you just want to create a new column with the Time values from the field Created.

That would be:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Shift", each DateTime.Time([Created]))

The keyword "each" in this example refers to the current record of the #"Changed Type" and any subsequent field name is a field in that record, in this example "Created" of the current record.

Instead of each, you can use a function with a parameter: any name between parentheses followed by =>. I used (x) =>, but I could have used something more descriptive like "ChangedTypeCurrentRecord".

I had to use this alternative, because the "Created" field will be used within the Table.SelectRows function, which selects records from table ShiftTimings.

Without the x, the field "Created" would have been considered as one of the fields in the table ShiftTimings.

With the x, I refer to the current record of #"Changed Type" or in other words:

the x allows me to carry the "Created field" into the Table.SelectRows function.

If I replace the x with ChangedTypeCurrentRecord, then the code looks like:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Shift", (ChangedTypeCurrentRecord) => Table.Last(Table.SelectRows(ShiftTimings, each [Start] <= DateTime.Time(ChangedTypeCurrentRecord[Created])))[Shift])

[Anonymous]

Is there a pbix file that I could download to view this?