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

    • nenandi's avatar
      nenandi
      Regular Visitor
      2 years ago

      yes I read your notes and also replaced column names

      here sorce:

      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],
      #"Changed Type" = Table.TransformColumnTypes(#"44405e26-d3b2-481a-ab18-0a9091600df9",{{"BaD", type number}})
      in
      #"Changed Type"

      • dufoq3's avatar
        dufoq3
        Community Champion
        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