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

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