Wrong input in a field Date

[gpiero]

I'm facing a very courios issue.

I have a list in Sharepoint where my Supplier write a Date.

 

The list is corretly formatted in Sharepoint.

When upload the data in Power BI I am getting this

Since the date it is not correct, whatever statistics wil be wrong.

I tought I have found the solution as showed below

DDT IssueDateNew = DATEVALUE(day('Third Party Delivery Notes'[DDT issue date]) + 1 &"/"& month('Third Party Delivery Notes'[DDT issue date]) & "/"& YEAR('Third Party Delivery Notes'[DDT issue date])) & " "& TIMEVALUE(hour('Third Party Delivery Notes'[DDT issue date]) - 22 &":"& MINUTE('Third Party Delivery Notes'[DDT issue date]) &":"& SECOND('Third Party Delivery Notes'[DDT issue date]))

But it does not work when the date to evaluate is 31/03/2016: Power BI calculate 32/03/2016!!.

So my question is: which is the best practise to solve this issue from the root? Why a field date in Sharepoint is managed in this way by PBI?

Thanks in advance for any suggestion.

[ankitpatira]

@gpiero Fact that powerbi calculates '32/03/2016' means that it is not identifying column as a date column. Have you checked that imported column is date type column ? From DAX you provided you're extract day out of date field and adding 1 to it. I see you've used outer DATEVALUE function but since it has returned back 32 means DATEVALUE function is probably not working. May I ask why you need to use that DAX to be able to format date ?

[gpiero]

@ankitpatira

I fear I have not explained clearly the basic issue. Let me try once again step by step.

My Supplier put the right date: 10/05/2016.

PBI write 09/05/2016 22:00:00 and here the issue arise.

Due to this PBI behavior the perfomance of my Supplier is not right

Here you are how the data has been imported in PBI

= Table.TransformColumnTypes(#"Rimosse colonne",{{"Id", Int64.Type}, {"DDT issue date", type datetime}, {"DDT approved date", type datetime}, {"FornitoriId", Int64.Type}})

 Anyway I'll check again importing the same data in a new table

[gpiero]

@ankitpatira

I have repeated the import

= Table.TransformColumnTypes(#"Ordinate righe",{{"DDT issue date", type date}})

Before applying the query, in the column DDT issue date you could find 12/05/2016 22:00:00. Now it is type date but should be 13/05/2016, because this is the data put in the Sharepoint.

Do you have some other suggestion? I did not trasformed yet the column DDT approved date in case we want try something else.

[gpiero]

@ankitpatira

thanks to your observation I realized that one of the two columns was wrongly defined.

In fact now I do not need to put DATEVALUE.

Anyway the basic issue of the datetime that come from Sharepoint list remain and I solved it ( temporarly I hope)  as shown below.

DDT IssueDateNew = day(('Third Party Delivery Notes'[DDT issue date]) + 1) &"/"& month('Third Party Delivery Notes'[DDT issue date]) & "/"& YEAR('Third Party Delivery Notes'[DDT issue date]) & " "& hour('Third Party Delivery Notes'[DDT issue date]) - 22 &":"& MINUTE('Third Party Delivery Notes'[DDT issue date]) &":"& SECOND('Third Party Delivery Notes'[DDT issue date])

I wonder if someone else has faced the same issue ad how it has been solved.

Thanks again

[yogeshne]

What is the DateTime format supported by Power BI to push data in that column?

[gpiero]

@yogeshne

Thank for your message.

The problem arose last May had two variable: the first one my poor knowledge at the beginning, the second one a behaviour the MS support has defined "by design of Sharepoint".

Now I solved temporary the problem using a workaround suggested by MS tech support.

It consist to add some code in the query that trasform properly the field Date manually inserted in the Sharepoint list.

[Anonymous]

Hi @gpiero,

Can you share your solution? I'm facing the same issue importing dates from Sharepoint lists. Both are DateTime columns, but I've 1 day difference like you.

[gpiero]

Hi @Anonymous

during the last months I tried several solutions, included the workaround proposed by MS Support.

I would like to share the last solution I found. I think it is the best one because you do not need to add a calculated column to your data.

I changed data connection on all my PBI report using the option ODATA feed from Get Data.

Source = SharePoint.Tables("https://MyCompany.sharepoint.com/sites/Wmsh/en/wkfmgt", [ApiVersion = 15]),
    #"9a424f75-b565-498f-a57f-220da7d75fdf" = Source{[Id="9a424f75-b565-498f-a57f-220da7d75fdf"]}[Items],

Here you will find the right value for your Datetime column.

Here the string to connect to the site the contains your Sharepoint list.

https://MyCompany.sharepoint.com/sites/Wmsh/en/WkfMgt/_vti_bin/listdata.svc

Regards

gpiero

[Anonymous]

@gpiero,

OData.Feed solution works like a charm, thanks for the tip.