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

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