cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesCook Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

Hi Fyouri,

 

I checked again and have made an update to step 3, the datatype on the new column should have been datetime, not date.

 

Proof it works on my side is that the original column contains unique values (I set this on the sharepoint list) However when imported, we get duplicates on dates where UTC+1 kicks in in UK late March. Then, when we return to UTC, we get a missing day. The new column contains no duplicates or missing columns enabling it to create a many to one relationship with other tables in the model.

 

Give it a try. I'd be interested to know if it works for you.

ianw1000 Regular Visitor
Regular Visitor

Re: SharePoint date differs from Power BI date

HI!

 

Thanks for the workaround! We have a SP list with Lease Renewal dates and they were coming through as 11:00pm on 31/03/2016 rather than at 00:00 on 01/04/2016 (just one instance of several)

 

This is clearly a bug with the later API. Has it been reported anywhere so I can add my twopenneth?

 

Thanks.

 

Ian Watkins

cmoosbrugger Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

is there any news on this item? I got the exact same issue and it is annoying.

 

how can i change the api from 15 back to 14? the funny fact is it was working yesterday and the days before and just with today (I actually do notknow if there was an update or not) it does not work anymore.

csabil Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

Ni, I've noticed the same yesterday. I'm not sure if it was there before, but strange behaviour... Changing the API to 14 could work, but then I have to recreate my entire data table...

I would be happy about a solution too.

 

In any case, my column in SharePoint is set to Date only, I'm not sure why it comes over with a time to PowerBI in the first place. 

ianw1000 Regular Visitor
Regular Visitor

Re: SharePoint date differs from Power BI date

You should just be able to edit the M code for the read from the SharePoint list and probably the first few lines of code without having to completely rewrite the query.

 

IIRC when I had to do this for another problem I started off a new query against the same List, changed the API to 14 and then expanded the data out until it was in the same structure as the original query. Then cut and paste the M code from the temporary query into the M code of the old query, probably adjust a few #stepname bits and off you go.

 

If I've misunderstood your problem, then please excuse my ramblings 🙂

csabil Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

You understood right, I think. But with the different API, somehow some column names are also different, for example gets an extra "0" to the end, so I think at the end of the day, it will be easier to make it again step by step.

 

But this basic issue should be still solved, as this is just a workaround.

alltasksIT Occasional Visitor
Occasional Visitor

Re: SharePoint date differs from Power BI date

HI Heny

I am using Power BI Desktop.

 

similar issue

In the query editor  -

right click date column

I changed Type to   date/time/timezone

then I did a 2nd step  -  change type - using localte where I picked   English (Aus)

 

So everything looks great in Power BI desktop - until I publish the report to the Power BI service where the date issues appears again

AleksanderPunt Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

Having the same issue!

When are they fixing this?? Or is it because of incorrect regional time settings within Power BI/Sharepoint?

 

We add items in the sharepoint list with current dates (probably at midnight), but an hour gets subtracted when importing the dataset in Power BI. So "Dec 1st - 00:00" becomes "Nov 30th - 23:00".

 

Irritating when doing monthly analysis.. A workaround is to create calculated columns in the sharepoint list with year, month and day - BUT it can't be used as a timestamp-filter within Power BI.

danielH Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

This issue was resolved, yet since this month update it started again. could you please have a look ? 

i check with the API 14 and it works. but this is just a work around. 

nate_bd Occasional Visitor
Occasional Visitor

Re: SharePoint date differs from Power BI date

I know this isn't strictly related, however there are similarities to what I've just encountered while working on an SPFx solution where we're duplicating SharePoint Events that have a recurrence set.

 

There appears to be a mismatch between the server generated timestamp (which appears to be UTC) and the FieldValuesAsX (X = Text or HTML). I believe that the base DateTime properties persist to maintain back end continuity, and the SharePoint Regional settings dictate what is seen within FieldValuesAsX.

 

Here's what I saw within the console.

 

odata.type:"SP.Data.EventsListItem"
EndDate:"2018-08-11T05:00:00Z" EventDate:"2018-08-10T21:00:00Z" FieldValuesAsText: EndDate:"11/08/2018 5:00 p.m." EventDate:"11/08/2018 9:00 a.m."
odata.type:"SP.FieldStringValues"

 

I don't know whether this will help anyone here, however this enabled me to track down my non-PowerBI issue that is similar...

 

I'll see myself out now 🙂

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)