Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power Query Changing Date Based on Hour Ending Time

I have data and am looking to change the data based upon the hour ending value. So if the date is 1/25/2019 with 12:00:00 AM Hour Ending (24), this should be 1/26/2019 00:00:00. How can transform the data in this way to be able to get this result? All help would be great! Thanks!

 

Need Help

2 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    Try this calculated column:

     

    Column = IF(TIME(HOUR('Table (3)'[Date]); MINUTE('Table (3)'[Date]); SECOND('Table (3)'[Date])) = TIME(0;0;0);
    'Table (3)'[Date] + 1; 'Table (3)'[Date])
     
    Ricardo
  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  Anonymous 

    You could use this way in edit queries as below:

    Step1:

    Add a custom column by this formula

    if [Hour Ending] = 24 then Date.AddDays([Date], 1) else [Date]

    Step2:

    Remove "Date" column and rename the new custom column as "Date"

    Result:

     

    here is sample pbix file, please try it.

     

     

    Regards,

    Lin