cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jonathangabe
Helper I
Helper I

Creating new Column for Shift by comparing Time from 2 dataset

Hello Everyone, I'm sorry if this is against the rules.
I already post a question before: https://community.powerbi.com/t5/Desktop/Create-a-new-column-for-shift-based-on-datetime-column-and-...

after searching for some solutions in these forums, I found someone post long ago that already asked the same thing that I ask now. https://community.powerbi.com/t5/Desktop/Comparing-Time-in-PowerBI-and-Creating-a-new-column/td-p/13... 

I try to use the solution in that post, but I got stuck, I don't know what's wrong with the formula. Here the contents in my Advanced Editor are as below:

let
Source = Excel.Workbook(File.Contents("C:\Users\jonathan.parulian\Downloads\Machine.xlsx"), null, true),
Machine_Schedule_Sheet = Source{[Item="Machine_Schedule",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Machine_Schedule_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Machine", type text}, {"Room", type text}, {"Start", type datetime}, {"End", type datetime}}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Added Custom" = Table.AddColumn(#"Removed Blank Rows", "Shift", each (x) => Table.Last(Table.SelectRows(Shift, each [Start Shift] <= DateTime.Time(x[Start])))[Shift])
in
#"Added Custom"

I don't know what to do next after I implement the solution that I got from that link.

jonathangabe_0-1634027265463.png

 

After I clicked the Function, what value should I input there?

jonathangabe_1-1634027305969.png

 

If I input some number or text, I got some warning error

jonathangabe_2-1634027518088.png

 

What should I do next? 

Thank you. 

1 ACCEPTED SOLUTION

@jonathangabe Try just to add custom column. Something like this:

Table.AddColumn(prev_step, "Shift", each if Time.From([start])>=Time.From("22:00:00") then 3 else if Time.From([start])>=Time.From("14:00:00") then 2 else if Time.From([start])>=Time.From("06:00:00") then 1 else 1).

 

You can make your conditions more flexible, this is just example.

View solution in original post

4 REPLIES 4
Marik
Solution Supplier
Solution Supplier

@jonathangabe It isn't clear what you expect to achieve...

@Marik thank you for your reply.

I want to create a column that defines the Shift category for each data I have. Below is the picture data that I have. 

Machine DataMachine Data

Shift time categoryShift time category

I'm sorry if I'm not clear in explaining what I want to achieve.

@jonathangabe Try just to add custom column. Something like this:

Table.AddColumn(prev_step, "Shift", each if Time.From([start])>=Time.From("22:00:00") then 3 else if Time.From([start])>=Time.From("14:00:00") then 2 else if Time.From([start])>=Time.From("06:00:00") then 1 else 1).

 

You can make your conditions more flexible, this is just example.

View solution in original post

@Marik Thank you for your reply.

I already try the solution you give, it's most likely the best solution I can use, maybe I won't have to use the other table that has the Shift Category, but this should work just fine.

Thank you so much for your help.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.