Power Query Changing Date Based on Hour Ending Time

[Anonymous]

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!

[v-lili6-msft · Accepted solution]

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

[camargos88]

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