Dates are coming in in different formats from Sharepoint

[Anonymous]

I'm a novice with Power BI but I have a problem with the sharepointlist I have as a data source that I can't find an answer to.

The dates below are entered in the same format in sharepoint. But when I connect the list into powerBI the date formats differ. They are [Created] in september and october only, but as you can see, the month (10) is placed where the day should be.

Same is true for the [Modified] column and other date columns. 

I've tried converting to date since it comes in as text but if doesn't correct the issue. 

Thanks in advance for any help! 

[Gordonlilj]

Hi,

You could try changing the type to local in the power query editor by right clicking the column, "change type" and then "using local" and switch to what you want.

You could also try (If the column is a date) to change the format under the modelling tab

[Anonymous]

First of all, thank you for your help! 

That does solve the issue at first glance, the formatting is now correct. 

However if I start working with the dates, for example with DATEDIFF it still displays the wrong answer. (see picture)

And if I try to convert the text data field into a date one, it changes once again. (only for the dates with day numbers <13)

I feel this is a strange issue since the data comes from Sharepoint and is autogenerated timestamps on different tasks. So the formatting should be the same. 

Exporting the data from sharepoint to excel solves the problem as well, but then I can't get live data from the sharepoint online list.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

If your date format in origin data source is like dd-mm-yyyy, you can add a culture parameter into the Table.TransformColumnTypes function, it will change the way power bi change text type into the datetime type, such as following example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWN7DUNzIwtFQwMrIyMAAiJR0lI0t9AwuIqIG5lYmZlam5UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CreatedDate = _t, ModiftDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreatedDate", type datetime}, {"ModiftDate", type datetime}},"fy-NL")
in
    #"Changed Type"

BTW, pbix as attached.

Best regards,

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

[Anonymous]

Awesome, exactly what I needed. 

Thank you!

[Gordonlilj]

Is the sharepoint list using the format day-month-year?

If so then i think the issue is that the format day-month-year often creates issues during data conversions as power bi is very likely interpreting it as month-day-year instead, which is why DATEDIFF gives the wrong numbers.

Now if it's the case that the sharepoint list is using day-month-year then one solution could possible be to change the date format in sharepoint to year-month-day (ISO 8601) for example. 

[Anonymous]

Hi Gordonlilj,

The problem is not the format of the date, the problem is that it comes in as different date formats. 

The first few lines (october entries) are dd/mm/yyy and the next ones (september entries) are in mm/dd/yyy.

BR

Emil

[Gordonlilj]

What result do you get if you turn off time intelligence and/or type detection in options and setting and import it again?