Forum Discussion

MikeOneScreen's avatar
MikeOneScreen
Regular Visitor
3 years ago

Time on Google Sheets is not showing up on Power Query

I have the following google sheet

 

However when I link it with PowerBI using Power Query as shown in the picture below

 

 

I face the following two issues:

1. If you notice the cell F2 of the Google Sheet, you would notice that it has the value 5/15/2023 1:21:30 PM. However, power query when imports from this sheet, changes the time value to 1:21:30 am. 

2. I tired a work around for the above issue be making two extra columns in the googe sheet titles 'Time Value' and 'Time Value in Text'. The Time value in google sheet when load into Power Query shows the following error

 

It seems like the google sheet is converting the time to decimal number rounded upto 4 number after the decimal point (despite me having the format of the respective column set to 'Time' in google sheets) which is causing the issue. 

 

I also created another column with the 'Time Value in Text' in Google Sheet to convert the time into a decimal number and then try to convert that decimal number back to a time value in power query. However, when I import the data the power query rounds the number up to four digits. For example, the number 45061.5565972222 is rounded up to 45061.5566 automatically. I have tried going to Transform tab of Power Query and changing the rounding of this column to 14 number with no effect. It seems like power query is importing the number rounded up to 4 digits after the decimal places which might be causing the error as well. 

 

I only want to import the Time from Google Sheet to Power Query with it changing PM to AM. 

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi MikeOneScreen,

     

    I can not reproduce the issue you've described.

    Can you make sure this option: detect columns types.... is deactivated. 

    Create a new query and connect to your file again.

    That will allow you to see the values that are brought in. If they appear correct, transform your "Time" column to a datetime value. If they are not correct check your system and browser Region settings.

     

    Hope that will help you to resolve it.