Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

2 ACCEPTED SOLUTIONS

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.

 

View solution in original post

Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

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
Not applicable

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"}})

 

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
Not applicable

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

Anonymous
Not applicable

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

iplaygod
Resolver I
Resolver I

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
Not applicable

Thanks but the timezones are all the sames.

AnkitBI
Solution Sage
Solution Sage

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

Anonymous
Not applicable

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.