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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
gpiero
Skilled Sharer
Skilled Sharer

Wrong input in a field Date

I'm facing a very courios issue.

 

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

 pict10.JPG

 

The list is corretly formatted in Sharepoint.

 

When upload the data in Power BI I am getting this

pic11.JPG

 

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.

 

 

 

 

If I can...
9 REPLIES 9
ankitpatira
Community Champion
Community Champion

@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 ?

@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

 

If I can...

@ankitpatira

 

I have repeated the import

 

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

 

pic12.JPG

 

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.

 

If I can...

@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

If I can...

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

@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.

 

 

If I can...
Anonymous
Not applicable

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.

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

If I can...
Anonymous
Not applicable

@gpiero,

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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