Change the date based on time stamp

[patelrr]

Hello,

I need to add a custom column to my data on Power BI Desktop where the date is adjusted based on the time stamp of a different column.

For example:

I have a Date column that displays the following information: DD/MM/YYYY HH:MM:SS AMorPM (Example: 8/2/2019 2:54:14 AM)

Now I need to add a column that would adjust the date to the previous day's date if the time is between 12:00:00AM and 6:00:00AM. So for the above exmaple, I would want the new column to display the date as 8/1/2019. If the time is beween 6:00:01AM and 11:59:59PM, then it should display the same date. 

What would the formula look like for the above request in a custom column?

I'm brand new to Power BI and don't understand the syntaxes very well. All help is greatly appreciated.

[mailo2000]

HI,

Let you can try FORMAT as my example below 

NewColumn = Format([Datetime field],"DD/MM/YYYY hh:mm AM/PM")

I think that should be able to help you. 

Thanks

[mailo2000]

HI,

Let you can try FORMAT as my example below 

NewColumn = Format([Datetime field],"DD/MM/YYYY hh:mm AM/PM")

I think that should be able to help you. 

Thanks

[Nathaniel_C · Accepted solution]

Hi @mailo2000 ,

If I understand your question, you would like a new column that uses a day earlier if the hour is 6 am or less.

You would like to go from the first pic to the second?

Here is what you need to paste into the Adv Editor. in Power Query

Basically I created a new col with a date a day earlier, then extracted the hour, then wrote a conditional statement that said if the hour is less than or equal to 6, select the earlier day, if not select the time stamp day.

If you are a beginner let me highly recommend @KenPuls book M is for (Data) Monkey  and @MattAllington book Supercharge Power BI

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2sVDA01VEwMjC0VLBIzFWK1YEKmkEFDUytjA3AwrkgYUOosKGJlQFQOBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Time", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.AddDays([Time],-1)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "1 day earlier"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "time only", each DateTime.Time([Time])),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Newdate", each if [time only] <= #time(6, 0, 0) then [1 day earlier] else [Time]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"time only", type time}, {"Newdate", type date}})
in
#"Changed Type2"

[patelrr]

Hi Nathaniel,

When I try to paste this code into the Advanced Editor, it keeps spitting out the message "Token Comma Expected". I'm not sure what this message means or how to go about debugging the code. There's already some code in the Editor from some columns I've created previously. The code that I already have on there is posted below. Can you tell me where in my code I should be inputting your code and what I should/shouldn't keep from your code? 

Side Note: The original column with Date and Time is called "START_TIME" in my dataset.

Below is my code.

let
Source = Sql.Database("lm-suddb001", "lsud"),
dbo_Line_Downtime_for_Quality = Source{[Schema="dbo",Item="Line_Downtime_for_Quality"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_Line_Downtime_for_Quality,{{"BRANDCODE", Int64.Type}}),
#"Line Type" = Table.AddColumn(#"Changed Type", "Line_Type", each if [LINE] = "Line 225" or [LINE] = "Line 226" or [LINE] = "Line 255" or [LINE] = "Line 256" or [LINE] = "Line 265" or [LINE] = "Line 266" then "Bag Lines" else if [LINE] = "Line 217" or [LINE] = "Line 237" or [LINE] = "Line 247" then "Tub Lines" else "Converter"),
#"Material Type" = Table.AddColumn(#"Line Type", "Material Type", each if [CAUSE_LEVELS_2_NAME] = "2D Camera" or [CAUSE_LEVELS_2_NAME] = "Bag Filling" or [CAUSE_LEVELS_2_NAME] = "Bag Film Cutting" or [CAUSE_LEVELS_2_NAME] = "Bag Opening" or [CAUSE_LEVELS_2_NAME] = "Bag Quality" or [CAUSE_LEVELS_2_NAME] = "Bag Stretching" or [CAUSE_LEVELS_2_NAME] = "Film transport" or [CAUSE_LEVELS_2_NAME] = "Film Unwinder" or [CAUSE_LEVELS_2_NAME] = "Bag Filling"
then "Bag"
else if [CAUSE_LEVELS_2_NAME] = "Pick and Place Tub Loader" or [CAUSE_LEVELS_2_NAME] = "Tub Quality" or [CAUSE_LEVELS_2_NAME] = "Tub Surge" or [CAUSE_LEVELS_2_NAME] = "Tub Unit Load Quality"
then "Tub"
else if [CAUSE_LEVELS_2_NAME] = "Brown Case Quality" or [CAUSE_LEVELS_2_NAME] = "Case Checkweigher" or [CAUSE_LEVELS_2_NAME] = "Case Forming" or [CAUSE_LEVELS_2_NAME] = "Case Loading" or [CAUSE_LEVELS_2_NAME] = "Case Quality" or [CAUSE_LEVELS_2_NAME] = "Case Sealer" or [CAUSE_LEVELS_2_NAME] = "Case Sealing" or [CAUSE_LEVELS_2_NAME] = "Die cut transfer to Mandril" or [CAUSE_LEVELS_2_NAME] = "Die cut extraction" or [CAUSE_LEVELS_2_NAME] = "Hood Extraction" or [CAUSE_LEVELS_2_NAME] = "Infeed Staging Conveyor" or [CAUSE_LEVELS_2_NAME] = "Quality" or [CAUSE_LEVELS_2_NAME] = "Robot End Effector/Unit Load Forming" or [CAUSE_LEVELS_2_NAME] = "Shelf Ready Case Quality" or [CAUSE_LEVELS_2_NAME] = "Transport case from Mandril to outfeed" or [CAUSE_LEVELS_2_NAME] = "Bag Filling"
then "Corrugate"
else if [CAUSE_LEVELS_2_NAME] = "Tray Extraction" or [CAUSE_LEVELS_2_NAME] = "Tray Quality"
then "Tray"
else if [CAUSE_LEVELS_2_NAME] = "Lid Application" or [CAUSE_LEVELS_2_NAME] = "Lid Quality" or [CAUSE_LEVELS_2_NAME] = "Bag Filling"
then "Lid"
else if [CAUSE_LEVELS_2_NAME] = "Zipper Delivery" or [CAUSE_LEVELS_2_NAME] = "Zipper Roll Quality"
then "Zipper"
else if [CAUSE_LEVELS_2_NAME] = "Bottom Film Web Path In Converter" or [CAUSE_LEVELS_2_NAME] = "Middle Film Web Path In Converter" or [CAUSE_LEVELS_2_NAME] = "White Base"
then "PVA Film"
else if [CAUSE_LEVELS_2_NAME] = "Pago"
then "TES Stickers"
else "Other"),
#"Renamed Columns" = Table.RenameColumns(#"Material Type",{{"IS_STOP", "Stops"}, {"CAUSE_LEVELS_3_NAME", "Cause Level 3"}, {"CAUSE_LEVELS_2_NAME", "Cause Level 2"}, {"CAUSE_LEVELS_4_NAME", "Cause Level 4"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "START_TIME", "START_TIME - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"START_TIME - Copy", "Time"}})
in
#"Renamed Columns1"

[patelrr]

Thank you Nathaniel_C. That is exactly what I was looking for. I will also check out the books you recommended!