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

  • gpiero's avatar
    gpiero
    Icon for Skilled Sharer rankSkilled Sharer
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

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

        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