Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
After I clicked the Function, what value should I input there?
If I input some number or text, I got some warning error
What should I do next?
Thank you.
Solved! Go to Solution.
@Anonymous 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.
@Anonymous It isn't clear what you expect to achieve...
@Anonymous 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.
I'm sorry if I'm not clear in explaining what I want to achieve.
@Anonymous 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.
@Anonymous 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |