Forum Discussion

patelrr's avatar
patelrr
Regular Visitor
6 years ago
Solved

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.

 

Need Help
  • Nathaniel_C's avatar
    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"

     

     

     

5 Replies

  • mailo2000's avatar
    mailo2000
    Frequent Visitor
    6 years ago

    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's avatar
      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's avatar
        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!

  • mailo2000's avatar
    mailo2000
    Frequent Visitor
    6 years ago

    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