Forum Discussion

Hussein_charif's avatar
Hussein_charif
Icon for Helper V rankHelper V
1 year ago
Solved

Power Bi service refresh returns blank table

i have a report published on pbi service with around 10 tables. at a specific scheduled refresh time in the morning, when the scheduled refresh finishes, 1 of those 10 tables which contains the main ...
Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    If you're able to refresh correctly in desktop but getting no rows in the service depending on when the refreshe is made, then it is a timezone difference between yours and the service's. You need to take into consideration when using volatile functions like today(), now and the M equivalents the timezone difference.

     

    The formula below returns the current utc time and adds 8 hours, +8 being my UTC offset, and then parses the date only.

    INT ( UTCNOW () + DIVIDE ( 8, 24 ) )

    Below is the M equivalent. Notice the need to remove the timezone prior to parsing the date as there is a bug in the service that still returns the UTC equivalent if TZ is not removed. 

    Date.From(DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + #duration(0,8,0,0)))

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

how about filtering from the query itself? Anything that filters a date/datetime column?

Hussein_charif's avatar
Hussein_charif
Icon for Helper V rankHelper V
1 year ago

yes there is a date filter, speaking of that when its refereshed the dates are blank and when i try to navigate with the date slicer (it's a between type), the dates that were highlited in black and can be navigated to become unusable, meaning i cannot press on that date again as if it doesnt exist in the table

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    If you're able to refresh correctly in desktop but getting no rows in the service depending on when the refreshe is made, then it is a timezone difference between yours and the service's. You need to take into consideration when using volatile functions like today(), now and the M equivalents the timezone difference.

     

    The formula below returns the current utc time and adds 8 hours, +8 being my UTC offset, and then parses the date only.

    INT ( UTCNOW () + DIVIDE ( 8, 24 ) )

    Below is the M equivalent. Notice the need to remove the timezone prior to parsing the date as there is a bug in the service that still returns the UTC equivalent if TZ is not removed. 

    Date.From(DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + #duration(0,8,0,0)))

     

    • Hussein_charif's avatar
      Hussein_charif
      Icon for Helper V rankHelper V
      1 year ago

      thank you danextian, i'll work on the scheduled refreshes to make sure they aren't interrupted by a refresh from the server then