cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arvindlearns
Regular Visitor

Comparing Time in PowerBI and Creating a new column

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
MarcelBeug Community Champion
Community Champion

Re: Comparing Time in PowerBI and Creating a new column

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"

Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug Community Champion
Community Champion

Re: Comparing Time in PowerBI and Creating a new column

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])

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
MarcelBeug Community Champion
Community Champion

Re: Comparing Time in PowerBI and Creating a new column

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"

 

ShiftTimings.png

Specializing in Power Query Formula Language (M)
arvindlearns
Regular Visitor

Re: Comparing Time in PowerBI and Creating a new column

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

Highlighted
MarcelBeug Community Champion
Community Champion

Re: Comparing Time in PowerBI and Creating a new column

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"

Specializing in Power Query Formula Language (M)

View solution in original post

arvindlearns
Regular Visitor

Re: Comparing Time in PowerBI and Creating a new column

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 Community Champion
Community Champion

Re: Comparing Time in PowerBI and Creating a new column

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])

Specializing in Power Query Formula Language (M)

View solution in original post

jcozart91
Frequent Visitor

Re: Comparing Time in PowerBI and Creating a new column

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors