Forum Discussion

nenandi's avatar
nenandi
Regular Visitor
2 years ago
Solved

Create date column from 6AM to 5:59AM

Hi, How to create new column from column Created date where I have date and time to new AdjustedDate colum from 6AM to 5:59AM?
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Try this:

    let
    Source = SharePoint.Tables("https://vtts.sharepoint.com/sites/beltteam", [Implementation="2.0", ViewMode="Default"]),
    #"44405e26-d3b2-481a-ab18-0a9091600df9" = Source{[Id="44405e26-d3b2-481a-ab18-0a9091600df9"]}[Items],
    ChangedType = Table.TransformColumnTypes(#"44405e26-d3b2-481a-ab18-0a9091600df9",{{"BaD", type number}}),
    Ad_AdjustedDate = Table.AddColumn(ChangedType, "Adjusted Date", each 
        [ v_date = Date.From([Created]),
          v_time = Time.From([Created]),
          v_result = if v_time >= #time(0,0,0) and v_time <= #time(5,59,59) then Date.AddDays(v_date, -1) else v_date
        ][v_result], type date)
in
    Ad_AdjustedDate
nenandi's avatar
nenandi
Regular Visitor
2 years ago

I want all record eg. from 00:00 to 5:59 to be in previous day date

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi nenandi, like this?

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzDTNzIwMlEwMLAyMFCK1UEVNLIyxhQ0tTK1xBA0w6Ld0NzK0FQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]),
    ChangedTypeLocale = Table.TransformColumnTypes(Source,{{"Date Time", type datetime}}, "sk-SK"),
    Ad_AdjustedDate = Table.AddColumn(ChangedTypeLocale, "Adjusted Date", each 
        [ v_date = Date.From([Date Time]),
          v_time = Time.From([Date Time]),
          v_result = if v_time >= #time(0,0,0) and v_time <= #time(5,59,59) then Date.AddDays(v_date, -1) else v_date
        ][v_result], type date)
in
    Ad_AdjustedDate
    • nenandi's avatar
      nenandi
      Regular Visitor
      2 years ago

      Hi  dufoq3,

      I got this,

      but I need new column with date and all records and columns just to change date for records who past 00:00 till 5:59 to day before see example this 04-Jun should be 03-June (I use day from 6:00 to 5:59 if is records pass 00:00 I count it the day before).

       

       

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        It will work but you haven't applied my query to your data. If you don't know how to do it, read note below my post. Keep in mind that your column name is "Created" and I used "Date Time" in sample data. You have to change it in my query.