Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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/
Thanks. I ended up creating a custom column and defining it as =date.adddays([mydatecolumn],1)
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
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/
Thanks. I ended up creating a custom column and defining it as =date.adddays([mydatecolumn],1)
Thank you very much for the response. I'll look into it.
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.
Thanks but the timezones are all the sames.
Can you share M Query from Advanced Editor after hiding sensitive information
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"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |