Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Problem Converting UNIX time power bi desktop

  Hi,   In this topic explain how to convert the unix time type into power bi desktop:   https://community.powerbi.com/t5/Desktop/Converting-UNIX-time-to-Date-in-PowerBI-for-Desktop/m-...
General Comment
Interesting Links
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Shubs,

     

    The "2" is the adjustment of daylight savings. It should be the below one in your scenario. The time saving is an interval. You can adjust it yourself. 

     

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", 
each if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 
then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 5, 0, [UnixTime]/1000) 
else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 4, 0, [UnixTime]/1000)),

     

     

    Best Regards,
    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Shubs,

     

    I don't know the other boundary. That's why I asked you to adjust it. I assume it is April. It should be this one.

     

    if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 
    or Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) <= 4 
    then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 5, 0, [UnixTime]/1000) 
    else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 4, 0, [UnixTime]/1000)

     

    Best Regards,
    Dale

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-jiascu-msft

 

Thanks for clearing this. Just one quick one, that day saving hours changes with time( presently 4 hours and after november it will be 5 hours ), So do this formula work fine then as well ?

 

Also, Can you please explain the logic of '2' below :

#duration(0, 2, 0, [UnixTime]/1000)

 Thanks for the help. I appreciate your efforts.

 

Thanks
Shubs

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Shubs,

 

The "2" is the adjustment of daylight savings. It should be the below one in your scenario. The time saving is an interval. You can adjust it yourself. 

 

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", 
each if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 
then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 5, 0, [UnixTime]/1000) 
else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 4, 0, [UnixTime]/1000)),

 

 

Best Regards,
Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-jiascu-msft

     

    Thanks for your help !

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-jiascu-msft

     

    each if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11

    This will take the 5 hour day saving hours for just November and December only.. Not Jan, Feb or march ?

    As the the month number will be less than 11 ?

     

    Are you understanding the above thing ?

     

    Thanks
    Shubs

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Shubs,

       

      I don't know the other boundary. That's why I asked you to adjust it. I assume it is April. It should be this one.

       

      if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 
      or Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) <= 4 
      then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 5, 0, [UnixTime]/1000) 
      else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 4, 0, [UnixTime]/1000)

       

      Best Regards,
      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-jiascu-msft

         

        Thanks for your help