Change the date based on time stamp

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 ...

Need Help

Nathaniel_C
6 years ago
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"

Nathaniel_C
Community Champion
6 years ago
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
  Regular Visitor
  6 years ago
  Thank you Nathaniel_C. That is exactly what I was looking for. I will also check out the books you recommended!
- patelrr
  Regular Visitor
  6 years ago
  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"

Forum Discussion

Change the date based on time stamp

Related content

% change from previous time stamp

Return value based on time-stamp

Dynamic change Ranking based on Dimension change

Employees Stamp

Removing Time stamp from date