Forum Discussion

gpiero's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

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.     The list is corretly formatted in Sharepoint.   When upload the data in Power BI I am get...
Need Help
ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

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's avatar
    gpiero
    Icon for Skilled Sharer rankSkilled Sharer
    10 years ago

    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's avatar
      gpiero
      Icon for Skilled Sharer rankSkilled Sharer
      10 years ago

      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's avatar
        yogeshne
        New Member
        9 years ago

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