Power Query - Round Up Date


#1

Hi Guys,

Hoping one you guys are M/Power Query gurus…

So my data source is a SharePoint Online list and the dates are pulling through incorrectly, seems to be a none problem.

For example:
18/09/2018 23:00:00
18/09/2018 00:00:00

Where ever there’s a date that has 23:00:00 it should show the next day which would be 19/09/2018
, if its shows 00:00:00 then the date is correct so it would be 18/09/2018

I’ve changed SharePoint time zones, tried If then and else M query but can’t seem to crack it.

Maybe a custom function would do it??

Thanks for any help :slight_smile:


#2

Very interesting. Very odd.

I’ll have a think about how this could be solved and come back


#3

I played around with a few things.

Got it working using these steps.

Have a play around with them and see what works.

I first broke these out to make it easier

image

You could delete these as another step at the end.

Then the key here is this custom column formula

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

image

That gave me the correct answer

image

See how you go with this.


#4

Hi Sam,

Thanks - that done the job.

Just annoying I had 6 columns to do that against but oh well.

Cheers


#5

That’s annoying. Very bizarre that it comes out of the sharepoint like that.


#6

Yeah I agree, on the SharePoint list the data type is date not Date time. It’s a known issues seen quite a few issues around it in the community.

Hopefully they do something about it.