Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
patelrr
Regular Visitor

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

 

1 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"

 

time1.PNG

 

time2.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
mailo2000
Frequent Visitor

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

 

Capture1.PNG

 

 

 

 

mailo2000
Frequent Visitor

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

 

Capture1.PNG

 

 

 

 

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"

 

time1.PNG

 

time2.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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"

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors