discrepancy in date between the source of data (sharepoint list) and imported query in PBi Desktop

[Anonymous]

Hi,

I've got a sharepoint list with prepopulated entries in one of the columns. Basically, one of the columns has the dates of all working Fridays. Each Friday a staff member edits the row for that particular week and adds some data to other columns. There are a few lists like that, each for one department.

Now, I've imported the sharepoint lists to PowerBi Desktop to start setting up a report but the Date column seems to have different dates from the original sharepoint lists.

All the original sharepoint lists have the following dates (UK format):

13/09/2019

20/09/2019

27/09/2019

etc.

And all the imported datasets in PowerBi Desktop display the following:

12/09/2019

19/09/2019

26/09/2019

etc.

That applies to all 7 imported sharepoint lists.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Open Advanced editor, add statements there.

See how to use Advanced editor below

https://docs.microsoft.com/en-us/power-bi/desktop-query-overview

https://yodalearning.com/tutorials/advanced-editor-with-power-query/

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous 

Please refer to this similar thread to find some workarounds.

https://community.powerbi.com/t5/Desktop/SharePoint-date-differs-from-Power-BI-date/td-p/13856

https://community.powerbi.com/t5/Desktop/Wrong-input-in-a-field-Date/m-p/215986#M95632

In these threads, they discussed:

One possible cause is :

dates in the summer were changing to the previous day because an hour was being taken off due to daylight saving

Solution:

set the imported column to data type timezone and create a new calculated column based on it with a data type date;

Another may be: ApiVersion

Solution : Change to ApiVersion14

Another is :Limitaion of the Sharepoint connecter

Solution: Chage to use Odata connector

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Where exactly am I supposed to input the commands referenced in on of the links? I have opened a Power Query Editor in PBi Desktop  but cannot find where to put the commands. For example:

1. In Query Editor, rename the offending SharePoint list date column rawDate or similar

= ( Table.RenameColumns(#"Changed Type",{{"Date", "rawDate"}})

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Open Advanced editor, add statements there.

See how to use Advanced editor below

https://docs.microsoft.com/en-us/power-bi/desktop-query-overview

https://yodalearning.com/tutorials/advanced-editor-with-power-query/

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks. I ended up creating a custom column and defining it as =date.adddays([mydatecolumn],1)

[Anonymous]

Thank you very much for the response. I'll look into it.

[iplaygod]

Hi

i havent worked with sharepoint lists,
but could you issue be related to time zone settings?
https://sharepointmaven.com/sharepoint-time-zone/

If you have a list that (invisibly) uses a particular timezone, then you could end up with different dates when you export it to some other system. If your list uses a local time zone, then if it is read in as UTC (for example) the very same date values could look like they are -1 date compared to what you had.

Might be completely useless idea from me. but I have worked alot with dates and time zones typically mess things up in many scenarios.

[Anonymous]

Thanks but the timezones are all the sames.

[AnkitBI]

Can you share M Query from Advanced Editor after hiding sensitive information

[Anonymous]

Is that it?

let
    Source = SharePoint.Tables("https://#####################", [ApiVersion = 15]),
    #"9ee599d1-abf8-42d0-9327-b90b98b4c81c" = Source{[Id="9ee599d1-abf8-42d0-9327-b90b98b4c81c"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"9ee599d1-abf8-42d0-9327-b90b98b4c81c",{{"ID", "ID.1"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"GeneralDate", type date}})
in
    #"Changed Type"