Forum Discussion

Bokbob's avatar
Bokbob
Icon for Helper I rankHelper I
8 years ago
Solved

Date format issues in Power query

Hi All, i have a sharepoint list item with pick up time as 20/06/2017 09:00 AM. but my Logic app is inserting it in SQL table  as  '2017-06-19 23:00:00.000'

 

where as when am accessing that tables values power query some how changing it as  

19/06/2017 11:00:00 PM

 (May be US time zone)

 

but i really need to convert it as (show on my report as ) 20/06/2017 09:00 AM.

 

Please help.

 

 

 

 

 

Need Help
  • Bokbob's avatar
    Bokbob
    8 years ago

    Hi Farhan, Thank you for the reply. I have added DAX column by adding 10/24 it worked fine now.

     

     

3 Replies

  • FarhanAhmed1984's avatar
    FarhanAhmed1984
    Icon for Resolver III rankResolver III
    8 years ago

    It seems like TimeZone is different

     

    - Goto Add Column,

    - Click Custom Column

    - Add DateTime with Time Zone "DateTime.AddZone(YourDate,Zone)"

    - Click Ok

    - Change the Column Type to "DateTime"

    - Hide your old date column and use this one

     

    Now you will see correct date/time in your report.

     

     

     

    • Bokbob's avatar
      Bokbob
      Icon for Helper I rankHelper I
      8 years ago

      Hi Farhan, Thank you for the reply. I have added DAX column by adding 10/24 it worked fine now.

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi FarhanAhmed1984, hi @ all,

       

      i'm struggling with quite the same issue. I get my data from a sharepoint list with a lot of several dates, so it would be unsuitable to add a new column everytime for every single date. 

      Is there any possibility to undo the struggle by changing the settings for timezones or some such things?

       

      date example:

      Sharepoint: 31/10/2018 00:00:00

      -> PBI:         30/10/2018 

       

       

      Thanks in advance.